Quick Overview

This question evaluates practical SQL data-manipulation skills such as joins, aggregation, window functions, time-based filtering, deduplication, and computation of sales KPIs for rep-level analytics.

Write advanced SQL for sales support analytics

Company: Meta

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Write SQL for the following schema and tasks. Assume ANSI SQL with DATE_TRUNC and INTERVAL supported. Sample tables (minimal rows shown). accounts +------------+-----------+------------+---------+ | account_id | industry | size_bucket| region | +------------+-----------+------------+---------+ | 100 | Software | MM | West | | 101 | Healthcare| SMB | East | | 102 | Manufacturing| ENT | East | | 103 | Retail | MM | Central | | 104 | Finance | SMB | Central | +------------+-----------+------------+---------+ reps +--------+------------+--------+---------+----------+ | rep_id | hire_date | segment| region | quota_usd| +--------+------------+--------+---------+----------+ | 10 | 2024-09-01 | SMB | West | 500000 | | 11 | 2024-12-15 | MM | East | 800000 | | 12 | 2025-03-10 | SMB | Central | 400000 | +--------+------------+--------+---------+----------+ deals +---------+------------+--------+---------------------+---------------------+------------+--------+------------+---------+ | deal_id | account_id | rep_id | created_at | closed_at | stage | is_won | amount_usd | region | +---------+------------+--------+---------------------+---------------------+------------+--------+------------+---------+ | 1 | 100 | 10 | 2025-04-05 10:00:00 | 2025-04-20 09:00:00 | Closed Won | 1 | 12000 | West | | 2 | 100 | 10 | 2025-04-25 12:00:00 | 2025-05-10 08:00:00 | Closed Lost| 0 | 8000 | West | | 3 | 101 | 11 | 2025-05-02 11:00:00 | 2025-06-01 16:00:00 | Closed Won | 1 | 5000 | East | | 4 | 102 | 11 | 2025-06-15 09:00:00 | 2025-07-20 10:00:00 | Closed Lost| 0 | 20000 | East | | 5 | 103 | 12 | 2025-06-10 14:00:00 | 2025-06-22 10:00:00 | Closed Won | 1 | 15000 | Central | | 6 | 104 | 12 | 2025-07-01 08:30:00 | 2025-07-18 12:00:00 | Closed Lost| 0 | 0 | Central | +---------+------------+--------+---------------------+---------------------+------------+--------+------------+---------+ touches (duplicates possible on same day/channel) +----------+------------+--------+------------+---------+--------------------+ | touch_id | account_id | rep_id | touch_date | channel | is_primary_contact | +----------+------------+--------+------------+---------+--------------------+ | 1 | 100 | 10 | 2025-01-10 | email | 1 | | 2 | 100 | 10 | 2025-04-01 | call | 1 | | 3 | 100 | 10 | 2025-04-01 | call | 1 | | 4 | 101 | 11 | 2025-02-15 | webinar | 0 | | 5 | 101 | 11 | 2025-04-28 | call | 1 | | 6 | 102 | 11 | 2025-06-16 | email | 1 | | 7 | 103 | 12 | 2025-06-01 | call | 1 | | 8 | 104 | 12 | 2025-06-29 | email | 1 | +----------+------------+--------+------------+---------+--------------------+ Tasks: A) For Q2 2025 (2025-04-01 to 2025-06-30 inclusive), by rep_id compute: closed_deals, wins, win_rate, avg_deal_amount_usd, avg_sales_cycle_days. Include only deals with stage like 'Closed%' and amount_usd > 0. Return rows sorted by win_rate desc; break ties by higher avg_deal_amount_usd. B) Deduplicate touches by keeping the smallest touch_id per (account_id, touch_date, channel). Define first_touch_channel_2025 as the channel of the earliest deduped touch for that account in 2025. Join to deals closed in 2025 and produce a table: first_touch_channel, segment (from reps), closed_deals, wins, segment_win_rate, and then a final row per channel showing the segment-weighted win_rate across segments (weights = that channel's closed_deals per segment). Provide a single SQL that outputs both per-segment and overall rows (e.g., via GROUPING SETS or UNION ALL). C) Compute, for each rep, the Pearson correlation r across Q2 2025 weeks between prior-week call_count (channel='call') and current-week win_rate. Define week as DATE_TRUNC('week', date). Show SQL that: (1) builds weekly call counts; (2) builds weekly win_rate for deals closed that week; (3) lags call_count by 1 week within rep; (4) aggregates per rep over weeks to compute r using the closed-form formula r = (n*sum(xy)-sum(x)sum(y)) / sqrt((n*sum(x^2)-sum(x)^2)*(n*sum(y^2)-sum(y)^2)). Return rep_id and r, excluding reps with n < 5 weeks of overlapping data. D) Data quality checks: (i) list deals where closed_at < created_at; (ii) accounts whose first touch occurs after their first close date; (iii) any deal with region differing from its account's region. Provide SQL for each. E) Last-7-days activity as of today: Use today = '2025-09-01'. For the window 2025-08-26 to 2025-09-01 inclusive, list top 3 reps by count of distinct accounts touched via calls (deduped rule above). Break ties by higher total call count, then lower rep_id. Provide SQL.

Overview: This question evaluates practical SQL data-manipulation skills such as joins, aggregation, window functions, time-based filtering, deduplication, and computation of sales KPIs for rep-level analytics.

Rep-level Q2 2025 win-rate and sales-cycle metrics

Using the schema below, write a SQL query to compute per-rep performance metrics for Q2 2025 (from 2025-04-01 to 2025-06-30 inclusive). For each rep_id, consider only deals that: - Have closed_at between DATE '2025-04-01' and DATE '2025-06-30' (inclusive), - Have stage LIKE 'Closed%', and - Have amount_usd > 0. For those deals, output one row per rep with: - rep_id - closed_deals: count of qualifying closed deals - wins: count of qualifying deals where is_won = 1 - win_rate: wins / closed_deals (as a decimal) - avg_deal_amount_usd: average amount_usd over qualifying deals - avg_sales_cycle_days: average number of days between created_at and closed_at (closed_date - created_date) Return rows sorted by win_rate in descending order; break ties by higher avg_deal_amount_usd (also descending).

Tables

accounts(account_id INT, industry VARCHAR(50), size_bucket VARCHAR(10), region VARCHAR(20))

reps(rep_id INT, hire_date DATE, segment VARCHAR(20), region VARCHAR(20), quota_usd DECIMAL(12,2))

deals(deal_id INT, account_id INT, rep_id INT, created_at TIMESTAMP, closed_at TIMESTAMP, stage VARCHAR(50), is_won INT, amount_usd DECIMAL(12,2), region VARCHAR(20))

touches(touch_id INT, account_id INT, rep_id INT, touch_date DATE, channel VARCHAR(20), is_primary_contact INT)

Hints

  1. Filter deals to Q2 2025 using closed_at and the given stage and amount constraints before aggregating.
  2. Use date subtraction (closed_date - created_date) to get sales cycle in days, and cast sums to decimals to avoid integer division in win_rate.

First-touch channel vs. win-rate by segment with channel totals

Using the schema below, write a single SQL query to analyze win rates by first-touch channel and rep segment for deals closed in 2025. Steps: 1) Deduplicate touches by keeping only the row with the smallest touch_id per (account_id, touch_date, channel). 2) For each account, within calendar year 2025, define first_touch_channel_2025 as the channel of that account's earliest deduplicated touch in 2025 (smallest touch_date; break same-day ties by smallest touch_id). 3) Consider all deals whose closed_at is in 2025 (any stage). Join each deal to its account's first_touch_channel_2025 and to the reps table for the segment. 4) Produce a result set with columns: - first_touch_channel - segment (from reps) - closed_deals: count of deals for that (first_touch_channel, segment) - wins: count of deals with is_won = 1 - segment_win_rate: wins / closed_deals 5) In the same query, also output a final aggregated row per first_touch_channel that shows the segment-weighted win_rate across segments (i.e., overall wins / overall closed_deals per channel). For these rows, you may set segment = 'ALL'. Use GROUPING SETS or a similar technique so that both per-segment rows and per-channel total rows are returned from a single SQL statement.

Tables

accounts(account_id INT, industry VARCHAR(50), size_bucket VARCHAR(10), region VARCHAR(20))

reps(rep_id INT, hire_date DATE, segment VARCHAR(20), region VARCHAR(20), quota_usd DECIMAL(12,2))

deals(deal_id INT, account_id INT, rep_id INT, created_at TIMESTAMP, closed_at TIMESTAMP, stage VARCHAR(50), is_won INT, amount_usd DECIMAL(12,2), region VARCHAR(20))

touches(touch_id INT, account_id INT, rep_id INT, touch_date DATE, channel VARCHAR(20), is_primary_contact INT)

Hints

  1. Use ROW_NUMBER() to first deduplicate touches per (account_id, touch_date, channel), then again to pick the earliest 2025 touch per account.
  2. After joining deals to first-touch channels and reps, use GROUPING SETS (or CUBE/ROLLUP) to get both per-segment rows and per-channel totals in a single aggregation.

Weekly Q2 2025 correlation between prior-week calls and win rate

Using the schema below, compute for each rep the Pearson correlation coefficient r across Q2 2025 weeks between prior-week call_count and current-week win_rate. Definitions: - Consider only Q2 2025 dates: from DATE '2025-04-01' to DATE '2025-06-30' inclusive. - A week is defined as week_start = DATE_TRUNC('week', date). - call_count for a rep and week is the number of touches with channel = 'call' whose touch_date falls in that calendar window; group them by rep_id and week_start. - For win_rate, consider deals where closed_at is in Q2 2025, stage LIKE 'Closed%', and amount_usd > 0. For each rep_id and week_start = DATE_TRUNC('week', closed_at::date), compute win_rate = wins / closed_deals. - For each rep and week, define x = prior-week call_count (i.e., call_count for the previous week_start within that rep), and y = current-week win_rate. Tasks: 1) Build weekly_call_counts: (rep_id, week_start, call_count) for Q2 2025 call touches. 2) Build weekly_win_rates: (rep_id, week_start, win_rate) for Q2 2025 closed deals. 3) Within each rep, lag call_count by week_start to get prior_week_call_count, and join it to the matching weekly_win_rates for the same week, forming pairs (x, y). 4) For each rep, over all weeks where both x and y are non-null, compute the Pearson correlation r using the closed-form formula: r = (n*sum(xy) - sum(x)*sum(y)) / sqrt((n*sum(x^2) - sum(x)^2) * (n*sum(y^2) - sum(y)^2)) 5) Return rep_id and r, but exclude any reps where n < 5 (fewer than 5 weeks with overlapping data). Write a single SQL query (using CTEs is fine) that performs steps (1)–(5).

Tables

accounts(account_id INT, industry VARCHAR(50), size_bucket VARCHAR(10), region VARCHAR(20))

reps(rep_id INT, hire_date DATE, segment VARCHAR(20), region VARCHAR(20), quota_usd DECIMAL(12,2))

deals(deal_id INT, account_id INT, rep_id INT, created_at TIMESTAMP, closed_at TIMESTAMP, stage VARCHAR(50), is_won INT, amount_usd DECIMAL(12,2), region VARCHAR(20))

touches(touch_id INT, account_id INT, rep_id INT, touch_date DATE, channel VARCHAR(20), is_primary_contact INT)

Hints

  1. Build weekly call counts and weekly win rates in separate CTEs using DATE_TRUNC('week', ...), then use a window LAG on call_count to get prior_week_call_count per rep.
  2. After joining prior-week call counts to current-week win rates, aggregate per rep to compute the sums needed for the Pearson correlation closed-form formula and filter out reps with n < 5.

Data quality check: deals closed before they were created

Write a SQL query to list all deals where closed_at is earlier than created_at, indicating an invalid sales cycle. Return all columns from the deals table for such records.

Tables

accounts(account_id INT, industry VARCHAR(50), size_bucket VARCHAR(10), region VARCHAR(20))

reps(rep_id INT, hire_date DATE, segment VARCHAR(20), region VARCHAR(20), quota_usd DECIMAL(12,2))

deals(deal_id INT, account_id INT, rep_id INT, created_at TIMESTAMP, closed_at TIMESTAMP, stage VARCHAR(50), is_won INT, amount_usd DECIMAL(12,2), region VARCHAR(20))

touches(touch_id INT, account_id INT, rep_id INT, touch_date DATE, channel VARCHAR(20), is_primary_contact INT)

Hints

  1. Compare closed_at directly to created_at in a simple WHERE clause.
  2. You can SELECT * since the requirement is to list all details of problematic deals.

Data quality check: first touch after first close

Write a SQL query to find accounts whose first recorded touch occurs after their first recorded deal close date. For each such account, output: - account_id - first_touch_date: the earliest touch_date in touches for that account - first_close_date: the earliest closed_at date (cast to DATE) in deals for that account Only include accounts that have at least one touch and at least one deal.

Tables

accounts(account_id INT, industry VARCHAR(50), size_bucket VARCHAR(10), region VARCHAR(20))

reps(rep_id INT, hire_date DATE, segment VARCHAR(20), region VARCHAR(20), quota_usd DECIMAL(12,2))

deals(deal_id INT, account_id INT, rep_id INT, created_at TIMESTAMP, closed_at TIMESTAMP, stage VARCHAR(50), is_won INT, amount_usd DECIMAL(12,2), region VARCHAR(20))

touches(touch_id INT, account_id INT, rep_id INT, touch_date DATE, channel VARCHAR(20), is_primary_contact INT)

Hints

  1. Compute the earliest touch_date and earliest closed_at date per account in separate CTEs, then join them.
  2. Filter to rows where first_touch_date is greater than first_close_date to catch accounts touched only after their first closing.

Data quality check: deal region mismatches account region

Write a SQL query to identify any deals whose region value does not match the region of their associated account. For each such deal, output: - deal_id - account_id - deal_region (from deals.region) - account_region (from accounts.region)

Tables

accounts(account_id INT, industry VARCHAR(50), size_bucket VARCHAR(10), region VARCHAR(20))

reps(rep_id INT, hire_date DATE, segment VARCHAR(20), region VARCHAR(20), quota_usd DECIMAL(12,2))

deals(deal_id INT, account_id INT, rep_id INT, created_at TIMESTAMP, closed_at TIMESTAMP, stage VARCHAR(50), is_won INT, amount_usd DECIMAL(12,2), region VARCHAR(20))

touches(touch_id INT, account_id INT, rep_id INT, touch_date DATE, channel VARCHAR(20), is_primary_contact INT)

Hints

  1. Join deals to accounts on account_id to compare the two region columns.
  2. Filter rows where the region values differ and select the requested columns.

Last-7-days call activity: top reps by distinct accounts touched

Assume today is DATE '2025-09-01'. For the 7-day window from DATE '2025-08-26' to DATE '2025-09-01' inclusive, write a SQL query to list the top 3 reps by outreach via calls. Rules: - Work only with touches where channel = 'call' and touch_date between DATE '2025-08-26' and DATE '2025-09-01' inclusive. - Deduplicate touches by keeping only the row with the smallest touch_id per (account_id, touch_date, channel). - For each rep_id, compute: - distinct_accounts_touched: COUNT(DISTINCT account_id) over these deduplicated call touches. - total_calls: COUNT(*) over these deduplicated call touches. - Return the top 3 reps ordered by: 1) distinct_accounts_touched DESC, 2) total_calls DESC, 3) rep_id ASC (as tie-breaker). Output columns: - rep_id - distinct_accounts_touched - total_calls

Tables

accounts(account_id INT, industry VARCHAR(50), size_bucket VARCHAR(10), region VARCHAR(20))

reps(rep_id INT, hire_date DATE, segment VARCHAR(20), region VARCHAR(20), quota_usd DECIMAL(12,2))

deals(deal_id INT, account_id INT, rep_id INT, created_at TIMESTAMP, closed_at TIMESTAMP, stage VARCHAR(50), is_won INT, amount_usd DECIMAL(12,2), region VARCHAR(20))

touches(touch_id INT, account_id INT, rep_id INT, touch_date DATE, channel VARCHAR(20), is_primary_contact INT)

Hints

  1. First deduplicate call touches in the 2025-08-26 to 2025-09-01 window using ROW_NUMBER() over (account_id, touch_date, channel).
  2. Aggregate per rep to get distinct account count and total_calls, then apply the specified ORDER BY and limit to 3 rows.

Loading coding console...