Top 100 SQL Interview Questions with Interactive In-Browser Benchmarks

Write a SQL query to report the second highest distinct salary from the Employee table. If there is no second highest salary (for example, only 1 employee exists or all employees share the identical compensation), your query must return NULL instead of an empty result set.

A company wants to identify high earners in each department. A high earner in a department is defined as an employee who has a salary in the top 3 unique salaries for that department. Write a query to find the employees who are high earners in each of the departments.

Write a SQL query to find all active users who have logged in on at least 3 consecutive calendar days. Output user_id and the start and end dates of each qualifying streak.

Write a SQL query to calculate the Month-Over-Month (MoM) revenue growth rate for each calendar month. Return the calendar month formatted as YYYY-MM, current month revenue, previous month revenue, and the percentage growth rate rounded to 2 decimal places.

Given a double-entry bank transactions ledger with transaction_id, account_id, transaction_timestamp, and amount, calculate the real-time running balance after each transaction for every account.

Given an employee table with employee_id, name, and manager_id (where the CEO has manager_id IS NULL), write a recursive SQL query to output each employee, their direct manager name, and their hierarchy depth level starting from the CEO at Level 1.

Analyze an e-commerce event stream table containing session_id, event_type ('view_product', 'add_to_cart', 'start_checkout', 'purchase'), and event_time. Calculate the unique session count at each milestone and the percentage conversion drop-off between successive stages.

Given a customers table and an activity_logs table, write a query to identify all customers who registered at least 90 days ago but have logged zero activity events in the last 60 days.

Payment networks monitor merchant dispute rates. Write a SQL query to calculate the total processed volume, chargeback count, and chargeback-to-transaction ratio for each merchant with at least 50 total transactions. Flag merchants whose chargeback ratio exceeds 1.5% (the Visa dispute monitoring threshold).

Write a SQL query to calculate the DAU/MAU (Daily Active Users divided by Monthly Active Users) engagement ratio for the most recent date in the platform telemetry table. Return DAU, 30-day MAU, and the stickiness percentage.

Write a query to retrieve all active enterprise subscriptions paying at least **$1,500** in MRR. Return the following columns: 1. `subscription_id` 2. `company_name` 3. `plan_tier` 4. `monthly_amount` (renamed as `mrr`) Order the results by `monthly_amount` in **descending** order. If two subscriptions have the same MRR, break ties by `company_name` ascending.

Query the `leads` table to extract unique combinations of `acquisition_channel` and `country_code`. Requirements: - Exclude records where `acquisition_channel` is `NULL` or marked as `'unknown'`. - Exclude records where `country_code` is `NULL`. - Order the output by `acquisition_channel` ascending, then by `country_code` ascending.

Query the `emergency_visits` table for patients who have status `'waiting'` and are classified under triage levels `'Resuscitation'` or `'Emergent'`. Return the following columns: 1. `visit_id` 2. `patient_name` 3. `triage_level` 4. `vital_pulse` 5. `arrival_time` Order results by `arrival_time` ascending so the earliest arrivals are attended to first.

Filter the `fulfillment_queue` table for orders where: - `shipping_tier` is `'Express'` - `fulfillment_status` is **not** `'shipped'` - `total_price` is greater than or equal to **$250.00** Return: 1. `order_id` 2. `customer_email` 3. `total_price` 4. `fulfillment_status` Order the result by `total_price` descending. For ties, sort by `order_id` ascending.

Find all staff members in the `staff_roster` table who satisfy: 1. `department` is `'Engineering'` 2. `perf_rating` is greater than or equal to **4.5** 3. `base_salary` is strictly less than **$160,000.00** Return: 1. `emp_id` 2. `full_name` 3. `job_title` 4. `perf_rating` 5. `base_salary` Sort the output by `perf_rating` descending. If ratings match, sort by `base_salary` ascending.

Query the `wire_transfers` table to identify transfers where: - `amount_usd` is between **10,000.00** and **50,000.00** (inclusive) using `BETWEEN` - `risk_score` is greater than or equal to **75** - `destination_country` is **not** `'USA'` Return: 1. `transfer_id` 2. `destination_country` 3. `amount_usd` 4. `risk_score` Order by `risk_score` descending, then by `amount_usd` descending.

Find all qualifying drivers in `driver_profiles` who: - Are currently online (`is_online = true`) - Have vehicle tier in `'XL'` or `'Black'` - Have a driver `rating` of at least **4.85** - Have completed at least **100** `lifetime_trips` Return: 1. `driver_id` 2. `driver_name` 3. `vehicle_tier` 4. `rating` 5. `lifetime_trips` Order results by `rating` descending, then by `lifetime_trips` descending.

Find all records in the `organizations` table where: - `billing_tier` is one of `'Starter'`, `'Professional'`, or `'Enterprise'` - `stripe_customer_id` is `NULL` Return: 1. `org_id` 2. `org_name` 3. `billing_tier` 4. `created_at` Order the results by `created_at` ascending, then by `org_id` ascending.

Query the `products` table to find items where: - `category` equals `'Audio'` - `product_title` contains either `'Wireless'` or `'Bluetooth'` (using the SQL `LIKE` operator) - `retail_price` is strictly less than **100.00** Return: 1. `sku` 2. `product_title` 3. `retail_price` 4. `in_stock` Order the result by `retail_price` ascending, breaking ties by `sku` ascending.

Write a query to assign each customer in `loyalty_members` a loyalty tier based on `lifetime_spend`: - `>= 5000.00` -> `'Platinum'` - `>= 2000.00` -> `'Gold'` - `>= 500.00` -> `'Silver'` - Else -> `'Bronze'` Alias the generated column as `tier_name`. Return: 1. `member_id` 2. `first_name` 3. `last_name` 4. `lifetime_spend` 5. `tier_name` Order results by `lifetime_spend` descending, then by `member_id` ascending.

Filter the `inpatient_vitals` table for patients who meet either of these critical clinical conditions: 1. `oxygen_pct` is strictly less than **90** 2. **OR** (`systolic_bp` is strictly greater than **180** **AND** `heart_rate` is strictly greater than **120**) Return: 1. `patient_id` 2. `ward_unit` 3. `oxygen_pct` 4. `systolic_bp` 5. `heart_rate` Order results by `oxygen_pct` ascending, breaking ties by `patient_id` ascending.

Assign each dispute in `dispute_tickets` a `priority_level` based on these rules: - If `disputed_amount >= 1000.00` OR `reason_code = 'unauthorized_charge'` -> `'CRITICAL'` - Else if `disputed_amount >= 200.00` -> `'HIGH'` - Else -> `'STANDARD'` Return: 1. `dispute_id` 2. `account_id` 3. `disputed_amount` 4. `reason_code` 5. `priority_level` Order by `disputed_amount` descending, then by `dispute_id` ascending.

Write a query to retrieve all cross-border wire transfers from `wire_transfers` where: - `amount_usd >= 10000.00` - `compliance_status = 'pending_review'` Return: 1. `transfer_id` 2. `sender_account_id` 3. `recipient_country` 4. `amount_usd` Order the results by `amount_usd` descending, then by `transfer_id` ascending.

Write a query to find all inventory records in `product_inventory` where available stock is critically depleted: - `available_units < 20` - `supplier_lead_days > 3` Return: 1. `sku` 2. `product_name` 3. `available_units` 4. `supplier_lead_days` Order results by `available_units` ascending, then by `sku` ascending.

Write a query to retrieve all patient vital readings from `triage_vitals` where `systolic_bp >= 140`. Include a computed column named `hypertension_stage`: - If `systolic_bp >= 180`, classify as `'CRISIS'` - Otherwise, classify as `'STAGE_2'` Return: 1. `patient_id` 2. `systolic_bp` 3. `diastolic_bp` 4. `hypertension_stage` Order results by `systolic_bp` descending, then by `patient_id` ascending.

Write a query to retrieve all trial accounts in `saas_trials` where: - `days_until_expiration BETWEEN 1 AND 7` - `total_logins >= 5` Return: 1. `account_id` 2. `org_name` 3. `days_until_expiration` 4. `total_logins` Order results by `days_until_expiration` ascending, then by `total_logins` descending.

Aggregate the `customer_accounts` table for all **active** accounts. Calculate: 1. `tier` 2. `total_mrr`: Sum of `mrr` 3. `active_customers`: Count of accounts in that tier 4. `avg_seats`: Average `seat_count` rounded to 1 decimal place Filter to keep only tiers with **at least 2** active customers using a `HAVING` clause. Order the results by `total_mrr` in **descending** order.

Analyze order transactions in `orders` grouped by `category`. Calculate: 1. `category` 2. `total_orders`: Count of all orders in that category 3. `total_gmv`: Sum of `order_amount` 4. `refund_rate_pct`: Percentage of total order amount that was refunded, calculated as: `ROUND(100.0 * SUM(refunded_amount) / SUM(order_amount), 2)` Order results by `refund_rate_pct` in **descending** order.

Aggregate patient stays in the `ward_admissions` table grouped by `department_name`. Calculate: 1. `department_name` 2. `total_admissions`: Count of all admissions 3. `avg_stay_days`: Average of `stay_duration_days` rounded to 1 decimal place 4. `max_stay_days`: Maximum `stay_duration_days` in that department Only include departments that have **at least 2** admissions. Order results by `avg_stay_days` descending.

Group deliveries in `courier_deliveries` by `delivery_zone`. Calculate: 1. `delivery_zone` 2. `total_packages`: Count of all deliveries 3. `avg_transit_minutes`: Average `transit_minutes` rounded to 1 decimal place 4. `on_time_pct`: Percentage of deliveries marked as on-time (`is_on_time = true`): `ROUND(100.0 * SUM(CASE WHEN is_on_time THEN 1 ELSE 0 END) / COUNT(*), 1)` Order results by `on_time_pct` ascending to see troubled zones first.

Group records in `payroll_records` by `department`. Calculate: 1. `department` 2. `headcount`: Total employees in that department 3. `min_salary`: Minimum `base_salary` 4. `max_salary`: Maximum `base_salary` 5. `avg_salary`: Average `base_salary` rounded to 2 decimal places 6. `salary_spread`: The difference between `max_salary` and `min_salary` (`MAX(base_salary) - MIN(base_salary)`) Order results by `salary_spread` in **descending** order.

Group records in `app_events` by `subscription_tier`. Calculate: 1. `subscription_tier` 2. `total_events`: Total count of event records 3. `unique_workspaces`: Count of distinct `workspace_id` values 4. `avg_events_per_workspace`: `ROUND(CAST(COUNT(*) AS NUMERIC) / COUNT(DISTINCT workspace_id), 1)` Order results by `total_events` descending.

Group records in `checkout_orders` by `promo_code`. Calculate: 1. `promo_code` 2. `order_count`: Total orders using that code 3. `gross_sales`: Sum of `gross_amount` 4. `total_discount`: Sum of `discount_amount` 5. `net_revenue`: Difference between gross sales and discount (`SUM(gross_amount - discount_amount)`) Filter using a `HAVING` clause to only keep promotions that generated at least **$500.00** in `net_revenue`. Order results by `net_revenue` descending.

Group records in `pharmacy_dispensations` by `drug_category`. Calculate: 1. `drug_category` 2. `distinct_patients`: Count of unique `patient_id` recipients 3. `total_units_dispensed`: Sum of `units_dispensed` 4. `total_expenditure`: Sum of (`units_dispensed * unit_cost`) Order results by `total_expenditure` descending.

Group transactions in `atm_withdrawals` by `terminal_id` and `branch_location`. Calculate: 1. `terminal_id` 2. `branch_location` 3. `withdrawal_count`: Total count of withdrawal events 4. `total_cash_dispensed`: Sum of `dispensed_usd` 5. `avg_withdrawal_size`: `ROUND(AVG(dispensed_usd), 2)` Use `HAVING` to only include terminals where `total_cash_dispensed >= 20000.00` AND `withdrawal_count >= 3`. Order results by `total_cash_dispensed` descending.

Group rides in `ride_requests` by `vehicle_tier`. Calculate: 1. `vehicle_tier` 2. `total_requests`: Total ride requests in that tier 3. `completed_rides`: Count of rides where `status = 'completed'` 4. `completion_rate_pct`: Percentage of rides completed: `ROUND(100.0 * SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) / COUNT(*), 1)` Order results by `completion_rate_pct` descending.

Group records in `saas_contracts` by `industry_sector`. Calculate: 1. `industry_sector` 2. `churned_logos`: Count of accounts where `contract_status = 'churned'` 3. `lost_mrr`: Sum of `mrr_value` for churned accounts 4. `avg_lost_mrr`: Average `mrr_value` of churned accounts, rounded to 2 decimal places Filter records with `WHERE contract_status = 'churned'`. Use `HAVING` to only include industries where `SUM(mrr_value) >= 3000.00`. Order results by `lost_mrr` descending.

Group records in `merchant_orders` by `merchant_id`. Calculate: 1. `merchant_id` 2. `total_orders`: Count of all orders 3. `on_time_orders`: Count of orders where `hours_to_dispatch <= 48` 4. `sla_compliance_pct`: Percentage of orders dispatched within 48 hours, rounded to 1 decimal place Filter groups with `HAVING COUNT(*) >= 3` (minimum volume). Order results by `sla_compliance_pct` DESC, then by `merchant_id` ASC.

Group records in `card_authorizations` by `issuing_country` where `auth_status = 'declined'`. Calculate: 1. `issuing_country` 2. `declined_count`: Number of declined authorizations 3. `declined_volume_usd`: Sum of `amount_usd` for declined authorizations Filter groups with `HAVING COUNT(*) >= 2`. Order results by `declined_volume_usd` descending, then by `issuing_country` ascending.

Group records in `er_patient_encounters` by `acuity_level`. Calculate: 1. `acuity_level` 2. `total_patients`: Total patients in this acuity tier 3. `avg_wait_minutes`: Average `wait_minutes` rounded to 1 decimal place 4. `max_wait_minutes`: Maximum recorded `wait_minutes` Order results by `acuity_level` ascending.

Group records in `customer_subscriptions` by `billing_frequency`. Calculate: 1. `billing_frequency` 2. `total_subscriptions`: Total subscriptions 3. `churned_count`: Subscriptions with `status = 'churned'` 4. `churn_rate_pct`: Percentage of subscriptions churned, rounded to 2 decimal places Order results by `churn_rate_pct` descending.

Join `ad_campaigns` with `deals` to compute campaign revenue efficiency. Calculate: 1. `campaign_name` 2. `channel` 3. `budget_spent` 4. `revenue_generated`: Sum of `deal_value` for `'closed_won'` deals (coalesced to `0` if none) 5. `roas`: Return on Ad Spend, calculated as `ROUND(SUM(d.deal_value) / c.budget_spent, 2)` Only include campaigns where at least one closed-won deal exists. Order the output by `roas` in **descending** order.

Join the `hospital_patients` table with `physicians` on `attending_doctor_id = doctor_id`. Return: 1. `p.patient_mrn`: Medical record number 2. `p.patient_name` 3. `p.ward_room` 4. `d.doctor_name` 5. `d.specialty` Only include patients whose status is `'admitted'`. Order results by `p.ward_room` ascending, then by `p.patient_name` ascending.

Join `supplier_pos` with `warehouse_receipts` using a `LEFT JOIN` to identify purchase orders that have never been received. Return: 1. `po.po_number` 2. `po.vendor_name` 3. `po.total_order_cost` 4. `po.expected_delivery_date` Filter to only retain purchase orders where `r.receipt_id IS NULL`. Order results by `po.expected_delivery_date` ascending.

Join the `employees` table to itself (Self-Join) matching each employee's `manager_id` to the manager's `emp_id`. Return: 1. `e.emp_id` 2. `e.full_name` AS `employee_name` 3. `e.job_title` 4. `COALESCE(m.full_name, 'Executive Board')` AS `manager_name` Use a `LEFT JOIN` so employees without a manager (CEOs/founders) are retained. Order the results by `e.emp_id` ascending.

Join `cross_border_transfers` with `bank_accounts` on `beneficiary_acc_id = account_id`. Return: 1. `t.transfer_id` 2. `t.amount_usd` 3. `a.account_number` 4. `a.account_holder` 5. `a.kyc_status` Filter for: - `t.amount_usd >= 15000.00` - `a.kyc_status != 'Tier_3_Enhanced'` Order results by `t.amount_usd` descending.

Join `rides` with `drivers` and `vehicles` across three tables: - `rides.driver_id = drivers.driver_id` - `rides.vehicle_id = vehicles.vehicle_id` Return: 1. `r.ride_id` 2. `d.driver_name` 3. `v.license_plate` 4. `v.model` 5. `r.fare_amount` Filter to only include trips where `v.inspection_passed = false`. Order results by `r.fare_amount` descending, then by `r.ride_id` ascending.

1. Generate all pairs of plan tiers from `plans` and features from `features` using a `CROSS JOIN`. 2. Connect to `plan_entitlements` using a `LEFT JOIN` on both `plan_id` and `feature_id`. 3. Compute `is_enabled`: `COALESCE(pe.enabled, false)`. Return: 1. `p.plan_name` 2. `f.feature_key` 3. `COALESCE(pe.enabled, false)` AS `is_enabled` Order results by `p.plan_name` ascending, then by `f.feature_key` ascending.

Join `prescriptions` with `patient_allergies` where the prescription's `active_ingredient` matches the patient's recorded `allergen_substance` for the same patient (`p.patient_id = a.patient_id`). Return: 1. `p.prescription_id` 2. `p.patient_id` 3. `p.medication_name` 4. `a.allergen_substance` 5. `a.severity` Order results by `p.prescription_id` ascending.

Join `internal_ledger` with `bank_statement_lines` using a `FULL OUTER JOIN` on `transaction_ref`. Calculate: 1. `COALESCE(l.transaction_ref, b.transaction_ref)` AS `ref_code` 2. `l.ledger_amount` 3. `b.statement_amount` 4. `reconciliation_status`: - When `l.transaction_ref IS NULL` THEN `'Missing in Ledger'` - When `b.transaction_ref IS NULL` THEN `'Missing in Bank'` - ELSE `'Matched'` Order results by `ref_code` ascending.

Join `checkout_sessions` with `completed_purchases` using a `LEFT JOIN` on `session_id`. Find all abandoned sessions where: - `p.order_id IS NULL` - `s.cart_total_usd >= 100.00` Return: 1. `s.session_id` 2. `s.user_email` 3. `s.cart_total_usd` 4. `s.cart_item_count` Order results by `s.cart_total_usd` descending.

Self-join `flight_routes` (`leg1` and `leg2`) where `leg1.dest_hub = leg2.origin_hub` and `leg1.origin_hub != leg2.dest_hub`. Return: 1. `leg1.origin_hub` AS `start_city` 2. `leg1.dest_hub` AS `layover_hub` 3. `leg2.dest_hub` AS `destination_city` 4. `(leg1.flight_hours + leg2.flight_hours)` AS `total_flight_hours` Filter for routes starting in `'Chicago'`. Order results by `total_flight_hours` ascending, then by `destination_city` ascending.

Join `app_users` to `kyc_documents` using a `LEFT JOIN` on `user_id` where `doc_status = 'approved'`. Find users who lack approved KYC verification where `u.registered_days_ago >= 14`. Return: 1. `u.user_id` 2. `u.user_email` 3. `u.account_tier` 4. `u.registered_days_ago` Order results by `u.registered_days_ago` descending, then by `u.user_id` ascending.

Join `rideshare_drivers` to `vehicle_inspections` on `vehicle_id`. Filter for drivers who are currently `driver_status = 'active'` but whose `inspection_status = 'expired'`. Return: 1. `d.driver_id` 2. `d.driver_name` 3. `v.license_plate` 4. `v.days_since_inspection` Order results by `v.days_since_inspection` descending, then by `d.driver_id` ascending.

Join `surgical_cases` with `operating_suites` on `suite_id` and with `surgeons` on `surgeon_id`. Return: 1. `c.case_id` 2. `s.surgeon_name` 3. `s.specialty` 4. `o.suite_name` 5. `c.scheduled_hours` Order results by `c.scheduled_hours` descending, then by `c.case_id` ascending.

Self-join `order_items` (`p1` and `p2`) on `order_id` where `p1.product_id < p2.product_id` (to avoid duplicates and self-pairings). Group by the pair of products and count how many distinct orders contained both products. Return: 1. `p1.product_id` AS `product_a` 2. `p2.product_id` AS `product_b` 3. `pair_order_count`: Number of shared orders Filter for pairs with `pair_order_count >= 2`. Order results by `pair_order_count` descending, then by `product_a` ascending, then by `product_b` ascending.

For each sales date in `daily_sales`, compute a 3-day rolling average (the current day plus the preceding 2 days). Return: 1. `sale_date` 2. `daily_amount` 3. `rolling_3day_avg`: Average of `daily_amount` across `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`, rounded to 2 decimal places Order by `sale_date` ascending.

For each vital reading in `icu_telemetry`, compute the heart rate change compared to the patient's immediately preceding reading. Calculate: 1. `patient_id` 2. `reading_time` 3. `heart_rate` 4. `prev_heart_rate`: Heart rate from the preceding reading using `LAG()` partitioned by `patient_id` 5. `bpm_delta`: (`heart_rate - LAG(heart_rate) OVER (PARTITION BY patient_id ORDER BY reading_time)`) Order results by `patient_id` ascending, then by `reading_time` ascending.

For each transaction in `card_transactions`, compute the cumulative count of transactions executed on that specific card up to and including the current transaction. Return: 1. `txn_id` 2. `card_token` 3. `merchant_name` 4. `amount_usd` 5. `card_txn_sequence`: `COUNT(*) OVER (PARTITION BY card_token ORDER BY txn_time ASC)` Order results by `card_token` ascending, then by `card_txn_sequence` ascending.

Assign each driver in `driver_earnings` to an earnings quartile (1 to 4) using `NTILE(4)` ordered by `weekly_payout` descending. Return: 1. `driver_id` 2. `driver_name` 3. `weekly_payout` 4. `earnings_quartile`: `NTILE(4) OVER (ORDER BY weekly_payout DESC)` Order results by `weekly_payout` descending, then by `driver_id` ascending.

For each row in `cohort_expansions`, compute the running cumulative expansion revenue for that cohort. Return: 1. `cohort_name` 2. `activity_month` 3. `expansion_mrr` 4. `cumulative_expansion_mrr`: `SUM(expansion_mrr) OVER (PARTITION BY cohort_name ORDER BY activity_month ASC)` Order results by `cohort_name` ascending, then by `activity_month` ascending.

For each customer order in `customer_purchases`, determine the customer's first-ever order amount using `FIRST_VALUE()`. Return: 1. `order_id` 2. `customer_id` 3. `order_date` 4. `order_amount` 5. `initial_order_amount`: `FIRST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC)` Order results by `customer_id` ascending, then by `order_date` ascending.

For each emergency patient in `er_throughput`, assign a wait quartile from 1 to 4 based on `wait_minutes` ascending (Quartile 1 = shortest wait, Quartile 4 = longest wait). Return: 1. `patient_id` 2. `triage_acuity` 3. `wait_minutes` 4. `wait_quartile`: `NTILE(4) OVER (ORDER BY wait_minutes ASC)` Order results by `wait_minutes` ascending, then by `patient_id` ascending.

For each transaction in `account_ledger_lines`, compute the real-time running balance for that specific account. Return: 1. `line_id` 2. `account_id` 3. `amount` 4. `running_balance`: Cumulative sum of `amount` partitioned by `account_id` ordered by `line_id ASC` Order results by `account_id` ascending, then by `line_id` ascending.

For each trip in `driver_trip_logs`, look up the driver's previous trip dropoff time using `LAG()`. Return: 1. `trip_id` 2. `driver_id` 3. `pickup_time` 4. `dropoff_time` 5. `prev_dropoff_time`: `LAG(dropoff_time) OVER (PARTITION BY driver_id ORDER BY pickup_time ASC)` Order results by `driver_id` ascending, then by `pickup_time` ascending.

For each vital reading in `patient_vitals`, calculate a rolling 3-reading systolic blood pressure average using: `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` partitioned by `patient_id` and ordered by `reading_time` ascending. Return: 1. `reading_id` 2. `patient_id` 3. `reading_time` 4. `systolic_bp` 5. `rolling_avg_systolic`: `ROUND(AVG(systolic_bp) OVER (...), 1)` Order results by `patient_id` ascending, then by `reading_time` ascending.

For each record in `daily_account_snapshots`, use `LAG()` to find the previous recorded balance for that account. Compute `daily_net_change` as `(closing_balance - prev_closing_balance)`. Return: 1. `account_id` 2. `snapshot_date` 3. `closing_balance` 4. `daily_net_change` Order results by `account_id` ascending, then by `snapshot_date` ascending.

For each transaction in `customer_purchases`, use `ROW_NUMBER()` partitioned by `customer_id` and ordered by `order_time` ascending to calculate `order_sequence`. Include a boolean/text flag `is_first_order`: - `'YES'` if `order_sequence = 1` - `'NO'` otherwise Return: 1. `order_id` 2. `customer_id` 3. `order_time` 4. `amount` 5. `order_sequence` 6. `is_first_order` Order results by `customer_id` ascending, then by `order_sequence` ascending.

For each workspace in `saas_workspaces`, calculate the seat utilization percentage as `ROUND(active_seats * 100.0 / allocated_seats, 1)`. Assign `saturation_rank` using `DENSE_RANK()` partitioned by `plan_tier` and ordered by utilization percentage descending. Return: 1. `workspace_id` 2. `org_name` 3. `plan_tier` 4. `utilization_pct` 5. `saturation_rank` Order results by `plan_tier` ascending, then by `saturation_rank` ascending, then by `workspace_id` ascending.

Write a modular query using a Common Table Expression (`WITH ...`) to compute the overall customer churn rate across all accounts. Calculate: 1. `total_accounts`: Total number of customer accounts created 2. `churned_accounts`: Number of accounts with status `'churned'` 3. `churn_rate_pct`: Percentage of accounts churned, rounded to 2 decimal places: `ROUND(100.0 * churned_accounts / total_accounts, 2)`

Write a modular query using a CTE to aggregate each movement type from `mrr_movements`: 1. `new_mrr`: Sum of `movement_amount` where `movement_type = 'new'` 2. `expansion_mrr`: Sum of `movement_amount` where `movement_type = 'expansion'` 3. `contraction_mrr`: Sum of `movement_amount` where `movement_type = 'contraction'` 4. `net_new_mrr`: `new_mrr + expansion_mrr - contraction_mrr` Return these four metrics in a single summary row.

Build a multi-stage query using CTEs: 1. `first_orders`: Find the earliest `order_date` for each `customer_id` from `orders_log`. 2. `repeat_buyers`: Count how many customers made another purchase within 30 days after their first order (`o.order_date > f.first_date AND o.order_date <= f.first_date + INTERVAL '30 days'`). 3. Return: - `total_customers`: Count of distinct customers with a first order - `repeat_customers`: Count of distinct customers who placed a repeat order within 30 days - `repeat_rate_pct`: `ROUND(100.0 * repeat_customers / total_customers, 2)`

Using a CTE, find all patients in `admissions_record` who were discharged and subsequently readmitted within 30 days. Calculate: 1. `total_discharges`: Count of all discharge events 2. `readmissions_within_30d`: Count of readmissions occurring within 30 days of previous discharge 3. `readmission_rate_pct`: `ROUND(100.0 * readmissions_within_30d / total_discharges, 2)` Return these three values in a single summary row.

1. Write a CTE `client_balances` that computes the total balance for each user across all accounts in `financial_accounts`. 2. In the outer query, categorize clients into wealth tiers: - `>= 250000.00` -> `'Private Client'` - `>= 100000.00` -> `'Premier'` - ELSE -> `'Standard'` 3. Group by `wealth_tier`, counting how many clients fall in each tier and the total assets in that tier. Return: 1. `wealth_tier` 2. `client_count` 3. `total_tier_assets` Order results by `total_tier_assets` descending.

Write a query using a CTE to aggregate total transit time per delivery route in `route_stops`. Calculate: 1. `route_id` 2. `stop_count`: Total number of stops on the route 3. `total_driving_minutes`: Sum of `leg_minutes` 4. `exceeds_shift_limit`: Boolean flag `CASE WHEN SUM(leg_minutes) > 480 THEN true ELSE false END` Order results by `total_driving_minutes` descending.

Write a CTE query to filter and identify power users from `user_activity_stream`. Calculate: 1. `user_id` 2. `distinct_modules`: Count of distinct `module_name` used 3. `total_actions`: Total event count Only keep users where `distinct_modules >= 3` AND `total_actions >= 10`. Order results by `total_actions` descending, then by `user_id` ascending.

1. In a CTE `customer_ltv`, sum `total_spent` across all purchases in `ecom_orders` for each `customer_id`. 2. In the outer query, use `NTILE(4)` ordered by `lifetime_spend DESC` to assign an `ltv_quartile` (1 = top spenders, 4 = lowest spenders). Return: 1. `customer_id` 2. `lifetime_spend` 3. `ltv_quartile` Order results by `lifetime_spend` descending, then by `customer_id` ascending.

Join `operating_rooms` with `surgeries` using a CTE to aggregate utilized minutes. Calculate: 1. `room_name` 2. `allocated_minutes`: 480 (standard shift) 3. `utilized_minutes`: Sum of `duration_minutes` for surgeries conducted in that room 4. `utilization_pct`: `ROUND(100.0 * utilized_minutes / 480, 1)` Order results by `utilization_pct` descending.

Write a Recursive CTE generating 4 monthly payment periods: - Period 1 starts with `remaining_balance = 1000 - 300 = 700.00`. - Each subsequent period increments `month_num` by 1 and reduces `remaining_balance` by 300.00. - Stop when `month_num = 4` (final balance = `1000 - 1200`, or remaining amount). Return: 1. `month_num` 2. `payment_amount`: 300.00 3. `remaining_balance` Order by `month_num` ascending.

Write a query using a CTE to evaluate inventory health for each product in `warehouse_stock`: 1. In a CTE `velocity`, calculate: - `sku` - `current_stock` - `daily_sales_velocity`: `daily_sales` - `days_of_supply`: `ROUND(current_stock / daily_sales, 1)` 2. In the outer query, filter for products where `days_of_supply <= 10.0` (urgent replenishment risk). Return: 1. `sku` 2. `product_name` 3. `current_stock` 4. `days_of_supply` Order results by `days_of_supply` ascending, then by `sku` ascending.

Write a query using a CTE `dept_payroll` that computes total payroll and headcount per department from `dept_employees`. Join the CTE with `dept_budgets` on `dept_id`. Return: 1. `b.dept_name` 2. `p.headcount` 3. `p.total_payroll` 4. `b.budget_cap` 5. `budget_utilization_pct`: `ROUND(p.total_payroll * 100.0 / b.budget_cap, 1)` Order results by `budget_utilization_pct` descending, then by `b.dept_name` ascending.

Write a query using a CTE `classified_loans` to categorize each loan in `consumer_loans` into an `aging_bucket`: - If `days_overdue >= 90`, bucket is `'90+ DPD'` - If `days_overdue >= 60`, bucket is `'60-89 DPD'` - If `days_overdue >= 30`, bucket is `'30-59 DPD'` - Otherwise, bucket is `'1-29 DPD'` In the outer query, group by `aging_bucket` where `days_overdue >= 1`. Return: 1. `aging_bucket` 2. `loan_count`: Total loans in bucket 3. `total_unpaid_principal`: Sum of `unpaid_principal` Order results by `total_unpaid_principal` descending.

Write a query using CTEs to compute the repeat purchase latency for each customer who made at least 2 orders: 1. In a CTE `ranked_orders`, use `ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC)` to rank orders chronologically. 2. In a second CTE `paired_orders`, join order 1 to order 2 for each customer and calculate the day difference (`o2.order_date - o1.order_date`). Return: 1. `customer_id` 2. `first_order_date` 3. `second_order_date` 4. `days_to_second_order` Order results by `days_to_second_order` ascending, then by `customer_id` ascending.

Write a recursive CTE to trace the delivery chain starting from the origin port where `leg_number = 1`. Accumulate `cumulative_miles` by adding each leg's distance. Return: 1. `cargo_id` 2. `leg_number` 3. `port_code` 4. `cumulative_miles` Order results by `cargo_id` ascending, then by `leg_number` ascending.

Query the `telemetry_logs` table to extract fields from the `payload` JSONB column. Extract: 1. `log_id` 2. `feature_name`: Extracted as text using `payload->>'feature'` 3. `duration_ms`: Extracted as integer using `(payload->>'duration_ms')::int` 4. `status`: Extracted as text using `payload->>'status'` Filter to only include records where: - `payload->>'feature'` equals `'vector_search_v2'` - `payload->>'status'` equals `'success'` Order results by `duration_ms` descending to see slowest runs first.

Build a cohort retention summary tracking the **signup cohort month** and the **activity month**. Write a query that computes: 1. `cohort_month`: Truncate user `signup_date` to month (`DATE_TRUNC('month', u.signup_date)::date`) 2. `activity_month`: Truncate event `event_date` to month (`DATE_TRUNC('month', e.event_date)::date`) 3. `active_users`: Count of distinct `user_id`s active in that month Join `cohort_users` to `user_events` on `user_id`. Group by `cohort_month` and `activity_month`. Order by `cohort_month` ascending, then by `activity_month` ascending.

Join `outgoing_wires` with `sanctions_blacklist` matching on normalized uppercase name comparison (`UPPER(TRIM(w.beneficiary_name)) = UPPER(TRIM(s.sanctioned_entity_name))`). Return: 1. `w.wire_id` 2. `w.beneficiary_name` 3. `w.amount_usd` 4. `s.sanction_program` 5. `s.listing_date` Order results by `w.amount_usd` descending, then by `w.wire_id` ascending.

Query `service_incidents` to extract fields using PostgreSQL JSONB path operators: 1. `incident_id` 2. `incident_data#>>'{service, name}'` AS `service_name` 3. `incident_data#>>'{error, code}'` AS `error_code` 4. `(incident_data#>>'{error, http_status}')::int` AS `http_status` Filter for incidents where `incident_data#>>'{error, severity}' = 'FATAL'`. Order results by `incident_id` ascending.

Group records in `ecom_transactions` by `customer_id`. Calculate: 1. `customer_id` 2. `recency_days`: `DATE '2024-04-01' - MAX(order_date)` 3. `order_frequency`: `COUNT(*)` 4. `monetary_total`: `SUM(amount)` Order results by `monetary_total` descending, then by `customer_id` ascending.

Analyze patient records in `er_patient_journey`. Calculate: 1. `total_arrivals`: Count of all patient visits 2. `physician_examined`: Count where `examined_by_md = true` 3. `inpatient_admitted`: Count where `admitted_to_ward = true` 4. `admission_rate_pct`: Percentage of arrivals that converted to inpatient admission: `ROUND(100.0 * COUNT(*) FILTER (WHERE admitted_to_ward = true) / COUNT(*), 1)` Return in a single summary row.

Query `fleet_vehicles` to compute `miles_since_service` (`current_odometer - last_service_odometer`). Return: 1. `vehicle_id` 2. `license_plate` 3. `current_odometer` 4. `last_service_odometer` 5. `miles_since_service`: (`current_odometer - last_service_odometer`) Filter for vehicles where `miles_since_service >= 10000`. Order results by `miles_since_service` descending.

Group employees in `workforce_records` by `department`. Calculate: 1. `department` 2. `total_hires`: Total employee records in that department 3. `resigned_count`: Count of employees with `status = 'resigned'` 4. `turnover_rate_pct`: `ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'resigned') / COUNT(*), 1)` Order results by `turnover_rate_pct` descending, then by `department` ascending.

Join `exchange_orderbooks` for `exchange_a` and `exchange_b` on `pair_symbol`. Calculate: 1. `a.pair_symbol` 2. `a.best_bid` AS `exchange_a_bid` 3. `b.best_ask` AS `exchange_b_ask` 4. `(a.best_bid - b.best_ask)` AS `arbitrage_spread` 5. `spread_pct`: `ROUND(100.0 * (a.best_bid - b.best_ask) / b.best_ask, 2)` Filter for executable opportunities where `a.best_bid > b.best_ask`. Order results by `spread_pct` descending.

Group culture sensitivity records in `antibiogram_isolates` by `pathogen_name`. Calculate: 1. `pathogen_name` 2. `total_isolates`: Total samples tested 3. `susceptible_count`: Count where `interpretation = 'Susceptible'` 4. `susceptibility_pct`: Percentage of isolates susceptible: `ROUND(100.0 * COUNT(*) FILTER (WHERE interpretation = 'Susceptible') / COUNT(*), 1)` Order results by `susceptibility_pct` descending, then by `pathogen_name` ascending.

For each geographic zone in `zone_supply_demand`, compute `demand_supply_ratio` (`rider_requests::numeric / available_drivers`) and assign a surge multiplier: - If `demand_supply_ratio >= 3.0` THEN `2.5` - Else if `demand_supply_ratio >= 2.0` THEN `1.8` - Else if `demand_supply_ratio >= 1.5` THEN `1.3` - Else `1.0` Return: 1. `zone_id` 2. `zone_name` 3. `rider_requests` 4. `available_drivers` 5. `demand_supply_ratio`: `ROUND(rider_requests::numeric / available_drivers, 2)` 6. `surge_multiplier` Order results by `surge_multiplier` descending, then by `zone_id` ascending.

Identify bank accounts in `cash_deposits` that made **at least 2** cash deposits between **$9,000.00** and **$9,999.99**. Calculate: 1. `account_id` 2. `suspicious_deposits_count`: Count of deposits in that range 3. `total_structured_amount`: Sum of `deposit_amount` Filter deposits where `deposit_amount BETWEEN 9000.00 AND 9999.99`. Group by `account_id` and use `HAVING COUNT(*) >= 2`. Order results by `total_structured_amount` descending.

Write a query using a CTE to aggregate revenue metrics from `cohort_annual_revenue` for the `'2023'` cohort: - `starting_mrr`: sum of `starting_mrr` - `expansion_mrr`: sum of `expansion_mrr` - `contraction_mrr`: sum of `contraction_mrr` - `churned_mrr`: sum of `churned_mrr` Calculate `nrr_percentage` in the outer query as: `ROUND(((starting_mrr + expansion_mrr - contraction_mrr - churned_mrr) * 100.0) / starting_mrr, 2)`. Return: 1. `cohort_year` 2. `starting_mrr` 3. `ending_mrr`: `(starting_mrr + expansion_mrr - contraction_mrr - churned_mrr)` 4. `nrr_percentage` Order by `cohort_year` ASC.

For each customer in `customer_rfm_summary`, assign a `monetary_quartile` from 1 to 4 using `NTILE(4) OVER (ORDER BY total_spend DESC)`. Quartile 1 contains the top 25% highest spenders. Return: 1. `customer_id` 2. `total_orders` 3. `total_spend` 4. `monetary_quartile` Order results by `monetary_quartile` ascending, then by `total_spend` descending, then by `customer_id` ascending.

Write a query to calculate readmissions occurring between 1 and 30 days after initial inpatient discharge. 1. In a CTE `readmission_cases`, find hospital admissions (`a2`) for the same patient where `a2.admission_date > a1.discharge_date AND a2.admission_date <= a1.discharge_date + 30`. 2. In the outer query, calculate total index discharges and count of patients readmitted. Return: 1. `total_index_discharges`: Count of all records in `inpatient_discharges` 2. `readmitted_count`: Count of unique patients with a 30-day readmission 3. `readmission_rate_pct`: `ROUND(readmitted_count * 100.0 / total_index_discharges, 1)` (No ORDER BY needed for single-row summary).

For each merchant in `merchant_monthly_stats`, determine their `reserve_rate`: - If `chargeback_ratio_pct >= 2.0`, rate is `0.15` (15% holdback) - If `chargeback_ratio_pct >= 1.0`, rate is `0.08` (8% holdback) - Otherwise, rate is `0.00` (No reserve) Calculate `reserve_holdback_usd` as `ROUND(gross_volume_usd * reserve_rate, 2)`. Return: 1. `merchant_id` 2. `gross_volume_usd` 3. `chargeback_ratio_pct` 4. `reserve_rate` 5. `reserve_holdback_usd` Order results by `reserve_holdback_usd` descending, then by `merchant_id` ascending.