Skip to main content
Talentd

Bitwise User Permissions Analysis - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: user_permissions

+-------------+---------+
| 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 in any order.

The result format is shown in the following example.

 

Example:

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)

Approach Overview

Problem Overview: You are given a table where each user’s permissions are encoded as a bitmask. Each bit represents a capability (read, write, execute, admin, etc.). The task is to analyze these bitwise permission values using SQL and return aggregated information about which permissions are enabled.

Approach 1: Bit Decomposition with Shifts (O(n * b) time, O(1) space)

The straightforward approach inspects every permission bit individually. For each row, shift the permission integer right by the bit position and check the least significant bit using (permissions >> k) & 1. In SQL this usually appears as conditional expressions or repeated calculations for each permission flag. This works when the number of permission types is small and fixed. Time complexity is O(n * b) where n is the number of rows and b is the number of permission bits examined.

Approach 2: Bitwise AND Filtering (O(n) time, O(1) space)

The optimal solution relies on direct bitwise checks using permissions & mask. Each permission corresponds to a mask such as 1, 2, 4, 8, etc. In MySQL, you can test whether a permission exists by evaluating whether (permissions & mask) != 0. This allows filtering, counting, or grouping users based on enabled bits without decomposing the entire number. Because each row is evaluated once, the query runs in O(n) time with constant space.

This pattern works well for analytics queries such as counting how many users have a specific permission, checking combinations of permissions, or deriving permission flags dynamically. SQL engines efficiently evaluate bitwise expressions during scans, making the approach scalable even for large tables.

Problems like this commonly appear in database systems where compact bitmasks store feature flags or access control lists. Understanding bitwise operations also helps when working with permission models, feature toggles, or system flags stored as integers.

Related concepts include bitwise operations, SQL querying, and general database data modeling strategies.

Recommended for interviews: The bitwise AND filtering approach is what interviewers expect. It shows that you understand how permission bitmasks are designed and how to query them efficiently. Demonstrating the naive bit-decomposition approach first shows conceptual understanding, but the optimized bitwise check proves you can translate that idea into an efficient SQL query.

Solution

We can use the BIT_AND and BIT_OR functions to calculate common_perms and any_perms.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Bit Decomposition with ShiftsO(n * b)O(1)When analyzing each permission bit separately or when the query explicitly requires checking multiple fixed bit positions
Bitwise AND FilteringO(n)O(1)General case for permission checks, filtering users by specific flags, or aggregating permission counts efficiently

Video Solution

Leetcode MEDIUM 3204 - Bitwise User Permissions Analysis - Explained by Everyday Data Science • Everyday Data Science • 436 views views

Watch 3 more video solutions →

Frequently Asked Questions

Is Bitwise User Permissions Analysis easy or hard?
The problem is typically rated Medium because it requires understanding how bitmasks encode multiple boolean flags in a single integer. Once you recognize the pattern, the solution is straightforward using bitwise AND operations in SQL.
Bitwise User Permissions Analysis Python/Java solution
In programming languages like Python or Java, the same logic uses bitwise operators. For example, checking if a permission exists can be written as (permissions & mask) != 0. This operation runs in constant time and mirrors how SQL performs permission checks.
How to solve Bitwise User Permissions Analysis in O(n)?
Scan the table once and apply a bitwise AND condition to check the relevant permission mask. For example, (permissions & mask) != 0 determines whether a particular permission is active. Aggregations like COUNT or conditional SUM can then compute statistics for users with those permissions.
What is the best approach for Bitwise User Permissions Analysis?
The most efficient approach uses bitwise AND operations to check whether a specific permission bit is enabled. In SQL, expressions like (permissions & mask) != 0 allow you to test permission flags directly without decomposing the entire integer. This runs in O(n) time because each row is evaluated once.
Is Bitwise User Permissions Analysis asked at Google/Amazon/Meta?
Bitmask permission problems are common in interviews at large tech companies because they test understanding of bitwise logic and efficient data representation. While the exact problem title may vary, similar questions appear in database and systems design rounds at companies like Amazon and Google.
What data structure is used in Bitwise User Permissions Analysis?
The core representation is a bitmask stored as an integer. Each bit position represents a permission flag. SQL queries then use bitwise operators such as AND (&) to extract or test those flags during database scans.
What is the time complexity of Bitwise User Permissions Analysis?
The optimal SQL solution runs in O(n) time where n is the number of rows in the table. Each record performs a constant-time bitwise AND operation to determine whether a permission flag is enabled. Space complexity is O(1) because the query does not require additional data structures.

Ready to solve this problem?

Practice Bitwise User Permissions Analysis with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary