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 Permissions Analysis Overviewuser_permissions Tableuser_idpermissions152123743BIT_ANDCommon to ALLBIT_ORAvailable to ANYResultcommon_permsany_perms015Binary Analysis:5=0101, 12=1100, 7=0111, 3=0011AND=0000(0), OR=1111(15)
Understanding the Visualization
1
Input
User permissions as integers with binary representation
2
Bitwise Operations
Apply BIT_AND for common and BIT_OR for any permissions
3
Output
Single row with aggregated permission results
Key Takeaway
🎯 Key Insight: Use bitwise aggregate functions to efficiently analyze permission patterns across all users in a single query

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