LEFT JOIN
Expert
PRO
Employee Manager Lookup
Use a self LEFT JOIN to list every employee with their direct manager's first name. Return columns: id, first_name, manager_name. Order results by: e.id.
100 XPSolve challenge
Work through 42 expert SQL exercises in a structured sequence. Each challenge includes a clear problem, schema context and an interactive query editor.
Use a self LEFT JOIN to list every employee with their direct manager's first name. Return columns: id, first_name, manager_name. Order results by: e.id.
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 who purchased products from both Software and Infrastructure. Return columns: customer_name, category_count. Order results by: c.name.
Calculate the number of days between each customer's consecutive orders while retaining the current and previous order dates. Return columns: customer_id, order_id, order_date, previous_order_date, days_since_previous. Order results by: order_date, id) AS previous_order_date FROM orders) SELECT customer_id, order_id, order_date, previous_order_date, CASE WHEN previous_order_date IS NULL THEN NULL ELSE CAST(JULIANDAY(order_date) - JULIANDAY(previous_order_date) AS INTEGER) END AS days_since_previous FROM sequenced ORDER BY customer_id, order_date.
Find products whose order-item revenue exceeds 250. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 500. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 750. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 900. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 1200. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 1500. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 1800. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Find products whose order-item revenue exceeds 2500. Return columns: product_name, units_sold, revenue. Order results by: revenue DESC, product_name.
Reconcile ledger and scanner inventory movements by reference_code and product_id. Return records missing from either side or having different quantities. Emulate a full outer join in SQLite. Return reference_code, product_id, ledger_qty, scanner_qty and issue. Return columns: reference_code, product_id, ledger_qty, scanner_qty, issue.
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.
Produce one row for every calendar date from the first through last sale date, filling missing days with zero revenue. Calculate revenue for that date and the inclusive last seven calendar days. Return revenue_date, daily_revenue and rolling_7_day_revenue. Return columns: revenue_date, daily_revenue, rolling_7_day_revenue.
Calculate the mathematical median salary per department using SQLite-compatible window positioning. For odd counts use the middle value; for even counts average the two middle values. Return department_id, employee_count and median_salary rounded to 2 decimals. Return columns: department_id, employee_count, median_salary.
Payments are duplicates only when merchant_id, external_reference and amount match. Keep one best record per duplicate group using status priority confirmed > pending > failed, then source reliability bank > gateway > import, then processed_at DESC, then payment_id DESC. NULL external references are independent and must not collapse. Return the retained payment rows. Return columns: payment_id, merchant_id, external_reference, amount, status, source, processed_at.
Treat multiple logins by the same user on one calendar date as one active day. Find every user's longest consecutive-day login streak. If multiple islands tie, keep the earliest starting island. Return user_id, streak_start, streak_end and streak_days; users with one active day have a one-day streak. Return columns: user_id, streak_start, streak_end, streak_days.
Aggregate completed revenue per partner, order contributors by revenue DESC then partner_id, and calculate each row's cumulative percentage of total revenue. Classify contributors as first_50, next_30 or remaining according to the cumulative percentage after including that contributor. Return partner_id, revenue, cumulative_revenue, cumulative_percent and contributor_band. Return columns: partner_id, revenue, cumulative_revenue, cumulative_percent, contributor_band.
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.
Calculate each product's share of completed invoice revenue. Invoice lines define revenue; line_tags is one-to-many descriptive data and must not multiply line revenue. Return product_id, revenue and revenue_share. Products with multiple tags must remain at invoice-line grain. Return columns: product_id, revenue, revenue_share.
As of 2025-05-15, compare regional completed revenue with calendar-day-paced quarterly targets. Q1 is complete: 90/90 eligible days. Q2 uses April 1 through May 15 inclusive: 45/91 days. Return region, quarter, actual_revenue, quarterly_target, eligible_days, quarter_days, paced_target and attainment_rate. A zero target yields NULL attainment. Return columns: region, quarter, actual_revenue, quarterly_target, eligible_days, quarter_days, paced_target, attainment_rate.
Inventory level events set the product's level until the next event. As of 2025-01-10 00:00:00, a stockout interval begins when level becomes zero and ends at the next event changing it above zero; open stockouts end at the cutoff. Sequence same-time events by event_id. Return product_id, stockout_start, stockout_end and stockout_hours. Return columns: product_id, stockout_start, stockout_end, stockout_hours.
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.