Bitwise User Permissions Analysis - Problem

You are given a table user_permissions that contains user IDs and their permissions encoded as integers. Each bit in the permissions integer represents a different access level or feature that a user has.

Write a SQL query to calculate:

  • common_perms: The access level granted to all users. This is computed using a bitwise AND operation on all permissions.
  • any_perms: The access level granted to any user. This is computed using a bitwise OR operation on all permissions.

Return the result in any order.

Table Schema

user_permissions
Column Name Type Description
user_id PK int Primary key, unique identifier for each user
permissions int Integer encoding user permissions where each bit represents a different access level
Primary Key: user_id
Note: Each row represents a user and their permission levels encoded as bits in an integer

Input & Output

Example 1 — Multiple Users with Different Permissions
Input Table:
user_id permissions
1 5
2 12
3 7
4 3
Output:
common_perms any_perms
0 15
💡 Note:

Binary Analysis:

  • User 1: 5 = 0101 (binary)
  • User 2: 12 = 1100 (binary)
  • User 3: 7 = 0111 (binary)
  • User 4: 3 = 0011 (binary)

common_perms (BIT_AND): 0101 & 1100 & 0111 & 0011 = 0000 = 0

any_perms (BIT_OR): 0101 | 1100 | 0111 | 0011 = 1111 = 15
Example 2 — Users with Some Common Permissions
Input Table:
user_id permissions
1 7
2 3
3 11
Output:
common_perms any_perms
3 15
💡 Note:

Binary Analysis:

  • User 1: 7 = 0111 (binary)
  • User 2: 3 = 0011 (binary)
  • User 3: 11 = 1011 (binary)

common_perms (BIT_AND): 0111 & 0011 & 1011 = 0011 = 3 (bits 0 and 1 are common to all users)

any_perms (BIT_OR): 0111 | 0011 | 1011 = 1111 = 15 (at least one user has each permission)
Example 3 — Single User
Input Table:
user_id permissions
1 9
Output:
common_perms any_perms
9 9
💡 Note:

Single User Case: When there's only one user, both BIT_AND and BIT_OR return the same value - the user's permissions (9 = 1001 in binary). The common permissions and any permissions are identical.

Constraints

  • 1 ≤ user_id ≤ 1000
  • 0 ≤ permissions ≤ 2^31 - 1
  • At least one user exists in the table

Visualization

Tap to expand
Bitwise User Permissions Analysis INPUT: user_permissions user_id permissions 1 7 (0111) 2 5 (0101) 3 3 (0011) 4 15 (1111) Bit Positions: Bit 3 Bit 2 Bit 1 Bit 0 Read | Write | Delete | Admin ALGORITHM STEPS 1 Initialize Aggregates Start with first user's perms 2 Apply BIT_AND Aggregate all rows with AND 0111 AND 0101 = 0101 0101 AND 0011 = 0001 0001 AND 1111 = 0001 common_perms = 1 3 Apply BIT_OR Aggregate all rows with OR 0111 OR 0101 = 0111 0111 OR 0011 = 0111 0111 OR 1111 = 1111 any_perms = 15 4 Return Results Output both aggregate values FINAL RESULT common_perms 1 (0001 binary) any_perms 15 (1111 binary) Interpretation: ALL users have: - Read access (Bit 0) ANY user has: - All 4 permissions - (Read, Write, Del, Admin) Key Insight: BIT_AND finds permissions ALL users share (intersection) - only bits set in every row remain 1. BIT_OR finds permissions ANY user has (union) - any bit set in any row becomes 1 in result. SQL: SELECT BIT_AND(permissions) AS common_perms, BIT_OR(permissions) AS any_perms FROM user_permissions; TutorialsPoint - Bitwise User Permissions Analysis | Optimal Solution

Related Problems

Asked in
Amazon 28 Microsoft 15 Google 12
34.8K Views
Medium Frequency
~8 min Avg. Time
892 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