In role-based access control a role can be granted to another role: a row (granted_role, grantee_role) means grantee_role inherits everything of granted_role, transitively. Users get roles directly from user_roles. Grants may accidentally form a cycle. For each user return user_name, effective_roles (number of distinct roles: direct + inherited) and can_read_finance (1 if 'FINANCE_READER' is among them, else 0). Order by user_name.
| Column | Type |
|---|---|
| granted_role | TEXT |
| grantee_role | TEXT |
| granted_role | grantee_role |
|---|---|
| FINANCE_READER | ANALYST |
| ANALYST | SR_ANALYST |
| DEV | SR_ANALYST |
| PUBLIC_RO | DEV |
| PUBLIC_RO | ANALYST |
| Column | Type |
|---|---|
| user_name | TEXT |
| role_name | TEXT |
| user_name | role_name |
|---|---|
| asha | SR_ANALYST |
| bob | DEV |
| chen | ANALYST |
| dina | PUBLIC_RO |
| esha | FINANCE_READER |
| user_name | effective_roles | can_read_finance |
|---|---|---|
| asha | 5 | 1 |
| bob | 2 | 0 |
| chen | 3 | 1 |
| dina | 1 | 0 |
| esha | 1 | 1 |
Solve it in the live editor on this page — run on sample data, then submit against hidden tests.
‹ Merchants Dormant in March · Build SCD Type 2 From Daily Snapshots ›
{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.
Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
{{ cur.statement }}
No submissions yet. Press Evaluate to submit your query.
{{ solutionSql }}
{{ out.error }}
You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.