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 XPSolve challenge
Interactive SQL practice
SQL CTE Exercises
Use common table expressions to structure readable multi-step SQL queries. These 31 interactive challenges use real schemas and instant query validation.
31 SQL challenges
Open Practice ArenaCTE
Advanced
Top Three Salaries with CTE
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.
50 XPSolve challenge
CTE
Advanced
PRO
Customers Buying Every Product in a Category
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.
50 XPSolve challenge
CTE
Advanced
PRO
Re-engaged Customers After Ninety Days
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.
50 XPSolve challenge
CTE
Advanced
PRO
First Repeat Purchase Within Thirty 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.
50 XPSolve challenge
CTE
Advanced
PRO
Marketplace Buyer Repeat Rate
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.
50 XPSolve challenge
CTE
Advanced
PRO
Subscription Churn Candidates
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.
50 XPSolve challenge
CTE
Advanced
PRO
Product Adoption Within Fourteen 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.
50 XPSolve challenge
CTE
Advanced
PRO
Normalize Messy Campaign Codes
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.
50 XPSolve challenge
CTE
Expert
PRO
Customer Cohort Retention
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.
100 XPSolve challenge
CTE
Expert
PRO
Three Consecutive Months of Spending Growth
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.
100 XPSolve challenge
CTE
Expert
PRO
Recursive Organization Depth and Reporting Path
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.
100 XPSolve challenge
CTE
Expert
PRO
Orders Above the Customer's Rolling Historical Average
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.
100 XPSolve challenge
CTE
Expert
PRO
Suppliers Dominating Every Warehouse
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.
100 XPSolve challenge
CTE
Expert
PRO
Monthly SaaS Logo Retention
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.
100 XPSolve challenge
CTE
Expert
PRO
Revenue Retention by Signup Cohort
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.
100 XPSolve challenge
CTE
Expert
PRO
Trial-to-Paid Funnel Conversion
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.
100 XPSolve challenge
CTE
Expert
PRO
Customer Lifetime Revenue by Acquisition Channel
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.
100 XPSolve challenge
CTE
Expert
PRO
Month-over-Month Revenue Growth
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.
100 XPSolve challenge
CTE
Expert
PRO
Pareto Revenue Contributors
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.
100 XPSolve challenge
CTE
Expert
PRO
Support SLA Breach Rate by Priority
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.
100 XPSolve challenge
CTE
Expert
PRO
Duplicate Bank Transactions with Reversal Handling
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.
100 XPSolve challenge
CTE
Expert
PRO
Canonical Customer Identity from Conflicting Records
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.
100 XPSolve challenge
CTE
Expert
PRO
Detect Impossible Order Status Transitions
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.
100 XPSolve challenge
CTE
Expert
PRO
Orphaned and Inconsistent Foreign References
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.
100 XPSolve challenge
CTE
Expert
PRO
Sessionize Product Events with Thirty-Minute Gaps
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.
100 XPSolve challenge
CTE
Expert
PRO
Concurrent Active Subscriptions Peak
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.
100 XPSolve challenge
CTE
Expert
PRO
Merge Consecutive Delivery Delay Islands
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.
100 XPSolve challenge
CTE
Expert
PRO
Attribution to the Last Eligible Marketing Touch
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.
100 XPSolve challenge
CTE
Expert
PRO
Balanced Team Assignment Candidates
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.
100 XPSolve challenge
CTE
Expert
PRO
Customer State Machine Snapshot
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.
100 XPSolve challenge
Concept guide
How to practice SQL CTE
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.
What to master
- • Give each CTE one clear responsibility
- • Name intermediate columns and calculations clearly
- • Test each step before composing the final query
- • Use recursive CTEs only for genuinely recursive relationships
A reliable problem-solving workflow
- 1Break the problem into logical result sets.
- 2Write and test the first result set.
- 3Reference it from the next CTE or final SELECT.
- 4Verify that the composed query preserves the intended grain.
Is a CTE the same as a subquery?
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.
Does a CTE always improve SQL performance?
No. A CTE primarily improves structure and readability. Its performance depends on the database engine, query plan and how the CTE is used.