Problem

Table: user_permissions

1
2
3
4
5
6
7
8

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| permissions | int     |
+-------------+---------+
user_id is the primary key.
Each row of this table contains the user ID and their permissions encoded as an integer.

Consider that each bit in the permissions integer represents a different access level or feature that a user has.

Write a solution to calculate the following:

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

Return the result table inany order.

The result format is shown in the following example.

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

Input:

user_permissions table:

+---------+-------------+
| user_id | permissions |
+---------+-------------+
| 1       | 5           |
| 2       | 12          |
| 3       | 7           |
| 4       | 3           |
+---------+-------------+
     

Output:

+-------------+--------------+
| common_perms | any_perms   |
+--------------+-------------+
| 0            | 15          |
+--------------+-------------+

Explanation:

  • common_perms: Represents the bitwise AND result of all permissions:
    • For user 1 (5): 5 (binary 0101)
    • For user 2 (12): 12 (binary 1100)
    • For user 3 (7): 7 (binary 0111)
    • For user 4 (3): 3 (binary 0011)
    • Bitwise AND: 5 & 12 & 7 & 3 = 0 (binary 0000)
  • any_perms: Represents the bitwise OR result of all permissions:
    • Bitwise OR: 5 | 12 | 7 | 3 = 15 (binary 1111)

Solution

Method 1 – Bitwise AND/OR Aggregation

Intuition

To find the permissions common to all users, use bitwise AND across all rows. To find permissions granted to any user, use bitwise OR across all rows.

Approach

  • Use SQL aggregate functions BIT_AND and BIT_OR (or their equivalents) to compute the results.
  • If not available, use & and | in a reduction with SUM and MIN/MAX as a workaround.

Code

1
2

SELECT BIT_AND(permissions) AS common_perms, BIT_OR(permissions) AS any_perms
FROM user_permissions;

Complexity

  • ⏰ Time complexity: O(n)
  • 🧺 Space complexity: O(1)