CTE
Advanced
Top Earner Per Department (CTE)
Using a CTE, find the highest-paid employee in each department. Return columns: department_id, first_name, last_name, max_salary. Order results by: e.department_id.
50 XP
Use common table expressions to structure readable multi-step SQL queries. These 31 interactive challenges use real schemas and instant query validation.
Using a CTE, find the highest-paid employee in each department. Return columns: department_id, first_name, last_name, max_salary. Order results by: e.department_id.
Concept guide
A common table expression, introduced with WITH, gives a temporary name to a query result. CTEs make multi-step SQL easier to read, test and maintain.
Both can express intermediate results. A CTE is named at the beginning of a statement and is often easier to read or reuse within that statement.
No. A CTE primarily improves structure and readability. Its performance depends on the database engine, query plan and how the CTE is used.
Use a CTE to return the three highest-paid employees. Return columns: first_name, last_name, salary, salary_rank. Order results by: salary DESC) AS salary_rank FROM employees) SELECT first_name, last_name, salary, salary_rank FROM ranked WHERE salary_rank <= 3 ORDER BY salary_rank.
For each non-empty product category, return customers who bought every active product in that category through completed purchases. Multiple purchases of the same product count once. Return category and customer_id. Return columns: category, customer_id.
An activity is a login, purchase, or support event in customer_activity. A return event is re-engagement when it occurs at least 90 full days after that customer's immediately previous event; exactly 90 days qualifies. Return customer_id, return_event_id, return_at, previous_activity_at and inactivity_days. Return columns: customer_id, return_event_id, return_at, previous_activity_at, inactivity_days.
For each customer, identify the first completed purchase and the first completed purchase after it. Same-time purchases are sequenced by purchase_id. Return customers whose first repeat occurs within 30 calendar days inclusive, with first_purchase_id, repeat_purchase_id and days_to_repeat. Cancelled purchases do not count. Return columns: customer_id, first_purchase_id, repeat_purchase_id, days_to_repeat.
For each buyer's first completed marketplace order month, classify whether the buyer placed another completed order within 60 days after that first order. Cancelled and refunded orders do not qualify. Report cohort_month, first_time_buyers, repeat_buyers and repeat_rate. Return columns: cohort_month, first_time_buyers, repeat_buyers, repeat_rate.
As of 2025-06-01, flag active subscriptions whose renewal_date is within 14 days inclusive and whose latest qualifying product activity is at least 30 full days old. login and feature_use qualify. Cancelled subscriptions never qualify. Return account_id, renewal_date, last_activity_at and inactive_days. Return columns: account_id, renewal_date, last_activity_at, inactive_days.
For each signup, find the first feature_activated event occurring from signup_at through exactly 14 days later. Events before signup and other event types do not qualify. Duplicate adoption events must produce one result. Return account_id, signup_at, first_adoption_at and days_to_adoption for adopted accounts only. Return columns: account_id, signup_at, first_adoption_at, days_to_adoption.
Normalize raw campaign codes by trimming, uppercasing, removing an optional UTM_ prefix, removing spaces, hyphens and underscores, and removing an optional trailing _2025 before separator removal. Return each canonical_code having more than one distinct raw_code, with variant_count and alphabetically joined raw variants. Return columns: canonical_code, variant_count, raw_variants.
Calculate how many customers from each acquisition month (cohort) placed orders in their first month. Return columns: cohort_month, cohort_size, first_month_orders, retention_rate. Order results by: c.cohort_month.
Find customers and ending months where monthly spend increased in each of the last three month-to-month transitions (four consecutive calendar months with spend). Return customer_id, ending_month and ending_spend. Missing months break the sequence. Return columns: customer_id, ending_month, ending_spend.
Traverse the organization from every root and return each unit's depth, root unit, and full reporting path. Roots have depth 0. The hierarchy has arbitrary depth, so use WITH RECURSIVE. Return unit_id, unit_name, depth, reporting_path and root_manager. Return columns: unit_id, unit_name, depth, reporting_path, root_manager.
For each order, compare its amount with that customer's average amount across strictly preceding rows ordered by ordered_at then order_id. At the same timestamp, the lower order_id is historical for the higher ID. Return only orders above a non-NULL historical average: customer_id, order_id, order_amount, historical_average rounded to 2 decimals. Return columns: customer_id, order_id, order_amount, historical_average.
A supplier dominates a warehouse when its available_units equal the highest available_units among all suppliers in that active warehouse; ties dominate jointly. Return suppliers that dominate every active warehouse. A missing supplier-warehouse row fails coverage. Return supplier_id ordered ascending. Return columns: supplier_id.
For each month from February through April 2025, a logo is active when its subscription overlaps any day of that month: start_date is before the next month and cancelled_at is NULL or on/after the month start. Opening logos are active in the previous month; retained logos are those opening logos also active in the current month. Return month, opening_logos, retained_logos and logo_retention_rate. Return columns: month, opening_logos, retained_logos, logo_retention_rate.
Assign accounts to their signup month. For cohort ages 0 and 1, sum recognized monthly invoice revenue. Month-0 revenue is the fixed baseline; month-1 revenue may expand, contract or fall to zero. Return cohort_month, cohort_age, base_revenue, current_revenue and net_revenue_retention. Include zero-revenue age rows. Return columns: cohort_month, cohort_age, base_revenue, current_revenue, net_revenue_retention.
For each acquisition channel, count users with a trial_start and users whose earliest paid event after their earliest trial occurs within 14 days inclusive. Duplicate trial/paid events count once per user. Paid events before a trial do not qualify. Return channel, trial_users, converted_users and conversion_rate. Return columns: channel, trial_users, converted_users, conversion_rate.
Attribute each customer to exactly one acquisition channel using their earliest acquisition touch, breaking timestamp ties by touch_id. First calculate completed lifetime payment revenue per customer, including acquired customers with zero revenue. Then report channel, customers, total_lifetime_revenue and average_customer_ltv. Return columns: channel, customers, total_lifetime_revenue, average_customer_ltv.
Report every calendar month from January through April 2025. Recognized revenue is the sum of completed revenue entries; missing months are zero. Return month, revenue, previous_month_revenue, absolute_change and percentage_growth. The first month has NULL comparisons; when previous revenue is zero percentage_growth is NULL. Return columns: month, revenue, previous_month_revenue, absolute_change, percentage_growth.
Identify the smallest deterministic ordered set of customers needed to reach at least 80% of completed revenue. Order customers by revenue DESC then customer_id. Include the customer whose revenue crosses the 80% threshold, and exclude all later customers. Return customer_id, revenue, cumulative_revenue and cumulative_share. Return columns: customer_id, revenue, cumulative_revenue, cumulative_share.
Measure first-response SLA by priority: critical 2 hours, high 8 hours, normal 24 hours. SLA starts at ticket creation. The earliest agent_response counts; notes do not. Exactly at the limit is within SLA. Unresolved tickets are breaches as of 2025-06-10 12:00:00 and remain in the denominator. Return priority, total_tickets, breached_tickets and breach_rate. Return columns: priority, total_tickets, breached_tickets, breach_rate.
A reversal points to original_transaction_id and economically cancels that original when account_id matches and reversal amount is its exact negative. Invalid/unmatched reversals are exceptions. Among non-reversed originals, duplicates share account_id, merchant_reference, amount and posted_at; keep the lowest transaction_id and flag later records. Return transaction_id and issue for valid reversals, reversed originals, duplicate copies and invalid reversals; legitimate similar transactions with different timestamps remain effective. Return columns: transaction_id, issue.
Build one canonical identity per identity_key. For name, precedence is CRM > billing > support > legacy; for email, verified values outrank unverified, then billing > CRM > support > legacy. Within equal precedence choose updated_at DESC then record_id DESC. Missing fields never win. Return identity_key, canonical_name and canonical_email. Return columns: identity_key, canonical_name, canonical_email.
Sequence status events by occurred_at then event_id. Allowed adjacent transitions are CREATED→PAID, CREATED→CANCELLED, PAID→SHIPPED, PAID→CANCELLED, SHIPPED→DELIVERED, and duplicate same-status events. Return every invalid transition with order_id, previous_status, current_status and current_event_id. Return columns: order_id, previous_status, current_status, current_event_id.
Audit project assignments. organization_id and employee_id are mandatory; team_id may be NULL. Classify each invalid assignment as missing_employee, wrong_employee_organization, missing_team, or wrong_team_organization. If several apply, use that precedence order. Use robust joins rather than NOT IN. Return columns: assignment_id, issue.
Assign product events to user sessions. A new session starts only when the gap from the previous event is greater than 30 minutes; exactly 30 minutes stays in the session. Order same-time events by event_id. Return user_id, event_id, occurred_at and session_id starting at 1 per user. Return columns: user_id, event_id, occurred_at, session_id.
Find the timestamp and count of peak concurrent subscriptions. Intervals are half-open [start_at,end_at): an ending subscription is inactive before another starts at the same timestamp, so end events sort before starts. NULL end_at remains active. Return the earliest timestamp reaching the global peak. Return columns: peak_at, peak_active_subscriptions.
For each route, merge delay intervals that overlap or are directly consecutive (next start is at most one day after the current merged end). Use inclusive dates. Return route_id, island_start, island_end and inclusive delay_days. Return columns: route_id, island_start, island_end, delay_days.
For each conversion, attribute the latest eligible email, paid_search or partner touch at or before conversion and within the inclusive previous 7 days. Organic touches are ineligible; future touches are forbidden. If eligible touches share a timestamp, higher touch_id wins. Keep conversions with no touch using NULL attribution. Return columns: conversion_id, customer_id, touch_id, channel, touched_at.
Produce a deterministic rule-based candidate list, not a global optimization. Each team has remaining_capacity. Rank eligible candidate-team pairs by fit_score DESC then candidate_id; return only rows whose rank is within that team's capacity. A candidate may appear for multiple teams. Return columns: team_id, candidate_id, fit_score, candidate_rank.
At 2025-06-30 23:59:59 derive each customer's lifecycle state from three streams. Verification is true after the latest verify/unverify event. Subscription is active after the latest activated/reactivated event unless the later state is cancelled. Payment health uses the latest succeeded/failed payment. Events after the snapshot are excluded; same-time higher event_id wins. State priority: UNVERIFIED, then NO_ACTIVE_SUBSCRIPTION, then PAYMENT_AT_RISK, else ACTIVE. Return columns: customer_id, customer_state.