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

[{"language":"MySQL","code":"SELECT BIT_AND(permissions) AS common_perms, BIT_OR(permissions) AS any_perms\nFROM user_permissions;","lang":"sql","html":"<pre class=\"shiki shiki-themes github-light github-dark\" style=\"background-color:#fff;--shiki-dark-bg:#24292e;color:#24292e;--shiki-dark:#e1e4e8\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">SELECT</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> BIT_AND(permissions) </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">AS</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> common_perms, BIT_OR(permissions) </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">AS</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> any_perms</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">FROM</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> user_permissions;</span></span></code></pre>"}]

Complexity

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

1

Related Tags