Write SQL and Python, run instantly in your browser, and track your progress.
You are a Security Analyst for Microsoft's Azure Active Directory team. For a security compliance audit, you need to generate a report of all active users who have MFA enabled and belong to an active organization. The report must include standardized user and organization details for the auditors.
Your task is to join the users and organizations tables and produce a clean list that meets the following criteria:
active.mfa_enabled.The final report should be sorted by the organization's primary domain to group users from the same company.
You are a Security Analyst for Microsoft's Azure Active Directory team. For a security compliance audit, you need to generate a report of all active users who have MFA enabled and belong to an active organization. The report must include standardized user and organization details for the auditors.
Your task is to join the users and organizations tables and produce a clean list that meets the following criteria:
active.mfa_enabled.The final report should be sorted by the organization's primary domain to group users from the same company.
| Column Name | Type |
|---|---|
| user_id | INTEGER |
| org_id | INTEGER |
| TEXT | |
| full_name | TEXT |
| role | TEXT |
| seat_type | TEXT |
| status | TEXT |
| mfa_enabled | INTEGER |
| Column Name | Type |
|---|---|
| user_id | INTEGER |
| org_id | INTEGER |
| TEXT | |
| full_name | TEXT |
| role | TEXT |
| seat_type | TEXT |
| status | TEXT |
| mfa_enabled | INTEGER |
| user_id | org_id | full_name | role | seat_type | status | mfa_enabled | created_at | last_login | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | feng.ferrari@vividdata.app | Feng Ferrari | owner |
| user_id | org_id | full_name | role | seat_type | status | mfa_enabled | created_at | last_login | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | feng.ferrari@vividdata.app | Feng Ferrari | owner |
| Column Name | Type |
|---|---|
| org_id | INTEGER |
| org_code | TEXT |
| org_name | TEXT |
| industry | TEXT |
| size_bucket | TEXT |
| country | TEXT |
| Column Name | Type |
|---|---|
| org_id | INTEGER |
| org_code | TEXT |
| org_name | TEXT |
| industry | TEXT |
| size_bucket | TEXT |
| country | TEXT |
| org_id | org_code | org_name | industry | size_bucket | country | state | city | primary_domain | status | created_at |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ORG00001 | Vivid Data | software |
| org_id | org_code | org_name | industry | size_bucket | country | state | city | primary_domain | status | created_at |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ORG00001 | Vivid Data | software |
| user_id | org_id | email_lower | email_domain | org_domain_lower | role_upper |
|---|---|---|---|---|---|
| 94 | 23 | sebastian.park@coralindustries.tech | coralindustries.tech | coralindustries.tech | MEMBER |
| 95 | 23 | hana.wilson@yahoo.com | yahoo.com | coralindustries.tech | MEMBER |
| 96 |
| user_id | org_id | email_lower | email_domain | org_domain_lower | role_upper |
|---|---|---|---|---|---|
| 94 | 23 | sebastian.park@coralindustries.tech | coralindustries.tech | coralindustries.tech | MEMBER |
| 95 | 23 | hana.wilson@yahoo.com | yahoo.com | coralindustries.tech | MEMBER |
| 96 |
Showing first 5 of 25 rows. Active users with MFA from active organizations sorted by organization domain.
Showing first 5 of 25 rows. Active users with MFA from active organizations sorted by organization domain.
Your final output should be a result set that meets the following requirements:
1. Joining & Filtering:
users and organizations tables must be joined on org_id.users.status is 'active'.users.mfa_enabled is 1.organizations.status is 'active'.NULL email.Your final output should be a result set that meets the following requirements:
1. Joining & Filtering:
users and organizations tables must be joined on org_id.users.status is 'active'.users.mfa_enabled is 1.organizations.status is 'active'.NULL email.| created_at | TEXT |
| created_at | TEXT |
| last_login | TEXT |
| last_login | TEXT |
| full |
| full |
| active |
| active |
| 1 |
| 1 |
| 2025-07-29 |
| 2025-07-29 |
| 2025-08-31 20:18:24 |
| 2025-08-31 20:18:24 |
| 2 | 1 | tao@vividdata.app | Tao Schneider | viewer | full | invited | 1 | 2025-08-18 |
| 2 | 1 | tao@vividdata.app | Tao Schneider | viewer | full | invited | 1 | 2025-08-18 |
| 3 | 1 | fang.al-farsi@vividdata.app | Fang Al-Farsi | member | full | invited | 0 | 2025-07-18 |
| 3 | 1 | fang.al-farsi@vividdata.app | Fang Al-Farsi | member | full | invited | 0 | 2025-07-18 |
| 4 | 1 | rennakamura@vividdata.app | Ren Nakamura | member | full | deleted | 0 | 2025-06-17 |
| 4 | 1 | rennakamura@vividdata.app | Ren Nakamura | member | full | deleted | 0 | 2025-06-17 |
| 5 | 1 | ychoi@vividdata.app | Yan Choi | admin | read_only | active | 1 | 2025-07-02 |
| 5 | 1 | ychoi@vividdata.app | Yan Choi | admin | read_only | active | 1 | 2025-07-02 |
| state | TEXT |
| state | TEXT |
| city | TEXT |
| city | TEXT |
| primary_domain | TEXT |
| primary_domain | TEXT |
| status | TEXT |
| status | TEXT |
| created_at | TEXT |
| created_at | TEXT |
| mid |
| mid |
| US |
| US |
| MA |
| MA |
| Boston |
| Boston |
| vividdata.app |
| vividdata.app |
| active |
| active |
| 2025-06-07 |
| 2025-06-07 |
| 2 | ORG00002 | Edge Software | fintech | small | US | TN | Nashville | edgesoftware.tech | active | 2024-12-11 |
| 2 | ORG00002 | Edge Software | fintech | small | US | TN | Nashville | edgesoftware.tech | active | 2024-12-11 |
| 3 | ORG00003 | Wind Works | devtools | small | US | CA | San Francisco | windworks.io | trial | 2025-02-22 |
| 3 | ORG00003 | Wind Works | devtools | small | US | CA | San Francisco | windworks.io | trial | 2025-02-22 |
| 4 | ORG00004 | Motion Dynamics | legaltech | enterprise | US | CA | Los Angeles | motiondynamics.ai | active | 2025-02-24 |
| 4 | ORG00004 | Motion Dynamics | legaltech | enterprise | US | CA | Los Angeles | motiondynamics.ai | active | 2025-02-24 |
| 5 | ORG00005 | Upward Intelligence | fintech | mid | US | TX | Houston | upwardintelligence.com | active | 2025-07-19 |
| 5 | ORG00005 | Upward Intelligence | fintech | mid | US | TX | Houston | upwardintelligence.com | active | 2025-07-19 |
| 23 |
| 23 |
| pwu@coralindustries.tech |
| pwu@coralindustries.tech |
| coralindustries.tech |
| coralindustries.tech |
| coralindustries.tech |
| coralindustries.tech |
| MEMBER |
| MEMBER |
| 90 | 22 | raviwilson@corenetworks.com | corenetworks.com | corenetworks.com | OWNER |
| 90 | 22 | raviwilson@corenetworks.com | corenetworks.com | corenetworks.com | OWNER |
| 92 | 22 | feng@corenetworks.com | corenetworks.com | corenetworks.com | MEMBER |
| 92 | 22 | feng@corenetworks.com | corenetworks.com | corenetworks.com | MEMBER |
2. Transformations:
2. Transformations:
users.email column must be converted to lowercase and aliased as email_lower.email_domain column must be created by safely extracting the domain from the user's email.organizations.primary_domain column must be converted to lowercase and aliased as org_domain_lower.users.role column must be converted to uppercase and aliased as role_upper.users.email column must be converted to lowercase and aliased as email_lower.email_domain column must be created by safely extracting the domain from the user's email.organizations.primary_domain column must be converted to lowercase and aliased as org_domain_lower.users.role column must be converted to uppercase and aliased as role_upper.3. Columns:
3. Columns:
user_idorg_idemail_loweremail_domainorg_domain_lowerrole_upperuser_idorg_idemail_loweremail_domainorg_domain_lowerrole_upper4. Ordering:
4. Ordering:
org_domain_lower in ascending alphabetical order.user_id in ascending order.org_domain_lower in ascending alphabetical order.user_id in ascending order.