Bitwise User Permissions Analysis - Practice Coding Problems

Bitwise User Permissions Analysis - Problem

Database Medium

You work for a company that manages user permissions through a sophisticated bitwise permission system. Each user has permissions encoded as an integer where each bit represents access to a specific feature or resource.

Given a table of user permissions, you need to analyze the permission patterns across all users:

Common Permissions: Features that all users can access (bitwise AND of all permissions)
Any Permissions: Features that at least one user can access (bitwise OR of all permissions)

For example, if User A has permissions 5 (binary: 0101) and User B has permissions 3 (binary: 0011):

Common permissions: 5 & 3 = 1 (binary: 0001) - only the rightmost feature is accessible to both
Any permissions: 5 | 3 = 7 (binary: 0111) - three different features are accessible by at least one user

Goal: Write a SQL query to calculate both metrics for all users in the system.

Input & Output

example_1.sql — Basic Case

$ Input: user_permissions table: +---------+-------------+ | user_id | permissions | +---------+-------------+ | 1 | 5 | | 2 | 12 | | 3 | 7 | | 4 | 3 | +---------+-------------+

› Output: +--------------+----------+ | common_perms | any_perms| +--------------+----------+ | 0 | 15 | +--------------+----------+

💡 Note: Binary analysis: 5(0101) & 12(1100) & 7(0111) & 3(0011) = 0(0000). For OR: 5(0101) | 12(1100) | 7(0111) | 3(0011) = 15(1111). No permissions are common to all users, but all 4 bit positions are used by at least one user.

example_2.sql — All Same Permissions

$ Input: user_permissions table: +---------+-------------+ | user_id | permissions | +---------+-------------+ | 1 | 7 | | 2 | 7 | | 3 | 7 | +---------+-------------+

› Output: +--------------+----------+ | common_perms | any_perms| +--------------+----------+ | 7 | 7 | +--------------+----------+

💡 Note: When all users have identical permissions (7 = 0111), both common_perms and any_perms equal 7. All users share the same three permissions.

example_3.sql — Single User Edge Case

$ Input: user_permissions table: +---------+-------------+ | user_id | permissions | +---------+-------------+ | 1 | 15 | +---------+-------------+

› Output: +--------------+----------+ | common_perms | any_perms| +--------------+----------+ | 15 | 15 | +--------------+----------+

💡 Note: With only one user having permissions 15(1111), both common and any permissions equal 15, as this single user defines both the intersection and union of permissions.

Visualization

Tap to expand

Understanding the Visualization

User Permissions

Each user has a binary permission pattern

Find Common Access

Use AND operation to find areas accessible to ALL users

Find Any Access

Use OR operation to find areas accessible to ANY user

Security Analysis

Compare common vs any permissions for access control insights

Key Takeaway

🎯 Key Insight: BIT_AND finds the intersection (common to all), while BIT_OR finds the union (available to anyone). This analysis is crucial for security auditing and access control management.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all records using optimized aggregate functions

✓ Linear Growth

Space Complexity

O(1)

Only storing final aggregated results

✓ Linear Space

Constraints

1 ≤ number of users ≤ 10⁵
0 ≤ permissions ≤ 2³¹ - 1
user_id is the primary key
At least one user exists in the table

Asked in

a Amazon 45 ⊞ Microsoft 38 G Google 32 f Meta 25

The optimal solution uses SQL aggregate functions BIT_AND and BIT_OR to efficiently calculate permissions in a single query. BIT_AND finds permissions common to all users, while BIT_OR finds permissions available to any user. This leverages database optimizations for O(n) time complexity with minimal space usage.

Common Approaches

Approach	Time	Space	Notes
✓ Manual Bitwise Operations	O(n)	O(1)	Manually iterate through each bit position and check permissions
SQL Aggregate Functions (Optimal)	O(n)	O(1)	Use BIT_AND and BIT_OR aggregate functions in a single SQL query

Manual Bitwise Operations — Algorithm Steps

Initialize common_perms to all 1s (or first user's permissions)
Initialize any_perms to 0
For each user, apply AND operation to common_perms
For each user, apply OR operation to any_perms
Return the final results

Visualization

Tap to expand

Step-by-Step Walkthrough

Initialize

Set common_perms and any_perms to first user's permissions

Process Each User

Apply AND to common_perms and OR to any_perms

Final Result

Return calculated common and any permissions

Code -

solution.c — C

#include <stdio.h>

typedef struct {
    int common;
    int any;
} PermissionResult;

PermissionResult bitwisePermissionsAnalysis(int permissions[], int size) {
    PermissionResult result = {0, 0};
    
    if (size == 0) {
        return result;
    }
    
    result.common = permissions[0];
    result.any = permissions[0];
    
    for (int i = 1; i < size; i++) {
        result.common &= permissions[i];  // AND operation
        result.any |= permissions[i];     // OR operation
    }
    
    return result;
}

int main() {
    int perms[] = {5, 12, 7, 3};
    PermissionResult result = bitwisePermissionsAnalysis(perms, 4);
    printf("Common: %d, Any: %d\n", result.common, result.any);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all user records

✓ Linear Growth

Space Complexity

O(1)

Only storing two integer results

✓ Linear Space

Constraints

1 ≤ number of users ≤ 10⁵
0 ≤ permissions ≤ 2³¹ - 1
user_id is the primary key
At least one user exists in the table

28.4K Views

Medium Frequency

~15 min Avg. Time

856 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Manual Bitwise Operations — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler