cswork / libraryOpen practice workspace

Coding questions · Meta

Write advanced SQL for sales support analytics

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 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 = (nsum(xy)-sum(x)sum(y)) / sqrt((nsum(x^2)-sum(x)^2)(nsum(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.

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).

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
industryVARCHAR(50)NOT NULL
size_bucketVARCHAR(10)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idindustryregionsize_bucket
100SoftwareWestMM
101HealthcareEastSMB
102ManufacturingEastENT
103RetailCentralMM
104FinanceCentralSMB

Table reps

columntypeconstraints
rep_idINTPRIMARY KEY
hire_dateDATENOT NULL
segmentVARCHAR(20)NOT NULL
regionVARCHAR(20)NOT NULL
quota_usdDECIMAL(12,2)NOT NULL

Sample data:

hire_datequota_usdregionrep_idsegment
2024-09-01500000West10SMB
2024-12-15800000East11MM
2025-03-10400000Central12SMB

Table deals

columntypeconstraints
deal_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
created_atTIMESTAMPNOT NULL
closed_atTIMESTAMPNOT NULL
stageVARCHAR(50)NOT NULL
is_wonINTNOT NULL
amount_usdDECIMAL(12,2)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idamount_usdclosed_atcreated_atdeal_idis_wonregionrep_idstage
100120002025-04-20 09:00:002025-04-05 10:00:0011West10Closed Won
10080002025-05-10 08:00:002025-04-25 12:00:0020West10Closed Lost
10150002025-06-01 16:00:002025-05-02 11:00:0031East11Closed Won
102200002025-07-20 10:00:002025-06-15 09:00:0040East11Closed Lost
103150002025-06-22 10:00:002025-06-10 14:00:0051Central12Closed Won
10402025-07-18 12:00:002025-07-01 08:30:0060Central12Closed Lost

Table touches

columntypeconstraints
touch_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
touch_dateDATENOT NULL
channelVARCHAR(20)NOT NULL
is_primary_contactINTNOT NULL

Sample data:

account_idchannelis_primary_contactrep_idtouch_datetouch_id
100email1102025-01-101
100call1102025-04-012
100call1102025-04-013
101webinar0112025-02-154
101call1112025-04-285
102email1112025-06-166
103call1122025-06-017
104email1122025-06-298
100call1102025-08-269
101call1112025-08-2710
101call1112025-08-2711
102email1112025-08-2812
103call1122025-08-2913
103call1122025-08-3014
104call1122025-08-3015
104call1122025-09-0116

Expected output

One row per rep_id showing Q2 2025 closed-deal counts, wins, win_rate, average deal amount, and average sales cycle in days, sorted by win_rate DESC then avg_deal_amount_usd DESC.

avg_deal_amount_usdavg_sales_cycle_daysclosed_dealsrep_idwin_ratewins
150001211211
50003011111
10000152100.51

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.

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
industryVARCHAR(50)NOT NULL
size_bucketVARCHAR(10)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idindustryregionsize_bucket
100SoftwareWestMM
101HealthcareEastSMB
102ManufacturingEastENT
103RetailCentralMM
104FinanceCentralSMB

Table reps

columntypeconstraints
rep_idINTPRIMARY KEY
hire_dateDATENOT NULL
segmentVARCHAR(20)NOT NULL
regionVARCHAR(20)NOT NULL
quota_usdDECIMAL(12,2)NOT NULL

Sample data:

hire_datequota_usdregionrep_idsegment
2024-09-01500000West10SMB
2024-12-15800000East11MM
2025-03-10400000Central12SMB

Table deals

columntypeconstraints
deal_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
created_atTIMESTAMPNOT NULL
closed_atTIMESTAMPNOT NULL
stageVARCHAR(50)NOT NULL
is_wonINTNOT NULL
amount_usdDECIMAL(12,2)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idamount_usdclosed_atcreated_atdeal_idis_wonregionrep_idstage
100120002025-04-20 09:00:002025-04-05 10:00:0011West10Closed Won
10080002025-05-10 08:00:002025-04-25 12:00:0020West10Closed Lost
10150002025-06-01 16:00:002025-05-02 11:00:0031East11Closed Won
102200002025-07-20 10:00:002025-06-15 09:00:0040East11Closed Lost
103150002025-06-22 10:00:002025-06-10 14:00:0051Central12Closed Won
10402025-07-18 12:00:002025-07-01 08:30:0060Central12Closed Lost

Table touches

columntypeconstraints
touch_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
touch_dateDATENOT NULL
channelVARCHAR(20)NOT NULL
is_primary_contactINTNOT NULL

Sample data:

account_idchannelis_primary_contactrep_idtouch_datetouch_id
100email1102025-01-101
100call1102025-04-012
100call1102025-04-013
101webinar0112025-02-154
101call1112025-04-285
102email1112025-06-166
103call1122025-06-017
104email1122025-06-298
100call1102025-08-269
101call1112025-08-2710
101call1112025-08-2711
102email1112025-08-2812
103call1122025-08-2913
103call1122025-08-3014
104call1122025-08-3015
104call1122025-09-0116

Expected output

Rows at two levels of aggregation: (first_touch_channel, segment) and (first_touch_channel, 'ALL'). For each row, closed_deals is the number of deals closed in 2025 for that grouping, wins is the number with is_won = 1, and segment_win_rate = wins / closed_deals. 'ALL' rows show the segment-weighted win_rate per channel.

closed_dealsfirst_touch_channelsegmentsegment_win_ratewins
1callSMB11
1callALL11
1emailMM00
3emailSMB0.33331
4emailALL0.251
1webinarMM11
1webinarALL11

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 = (nsum(xy) - sum(x)sum(y)) / sqrt((nsum(x^2) - sum(x)^2) * (nsum(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).

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
industryVARCHAR(50)NOT NULL
size_bucketVARCHAR(10)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idindustryregionsize_bucket
100SoftwareWestMM
101HealthcareEastSMB
102ManufacturingEastENT
103RetailCentralMM
104FinanceCentralSMB

Table reps

columntypeconstraints
rep_idINTPRIMARY KEY
hire_dateDATENOT NULL
segmentVARCHAR(20)NOT NULL
regionVARCHAR(20)NOT NULL
quota_usdDECIMAL(12,2)NOT NULL

Sample data:

hire_datequota_usdregionrep_idsegment
2024-09-01500000West10SMB
2024-12-15800000East11MM
2025-03-10400000Central12SMB

Table deals

columntypeconstraints
deal_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
created_atTIMESTAMPNOT NULL
closed_atTIMESTAMPNOT NULL
stageVARCHAR(50)NOT NULL
is_wonINTNOT NULL
amount_usdDECIMAL(12,2)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idamount_usdclosed_atcreated_atdeal_idis_wonregionrep_idstage
100120002025-04-20 09:00:002025-04-05 10:00:0011West10Closed Won
10080002025-05-10 08:00:002025-04-25 12:00:0020West10Closed Lost
10150002025-06-01 16:00:002025-05-02 11:00:0031East11Closed Won
102200002025-07-20 10:00:002025-06-15 09:00:0040East11Closed Lost
103150002025-06-22 10:00:002025-06-10 14:00:0051Central12Closed Won
10402025-07-18 12:00:002025-07-01 08:30:0060Central12Closed Lost

Table touches

columntypeconstraints
touch_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
touch_dateDATENOT NULL
channelVARCHAR(20)NOT NULL
is_primary_contactINTNOT NULL

Sample data:

account_idchannelis_primary_contactrep_idtouch_datetouch_id
100email1102025-01-101
100call1102025-04-012
100call1102025-04-013
101webinar0112025-02-154
101call1112025-04-285
102email1112025-06-166
103call1122025-06-017
104email1122025-06-298
100call1102025-08-269
101call1112025-08-2710
101call1112025-08-2711
102email1112025-08-2812
103call1122025-08-2913
103call1122025-08-3014
104call1122025-08-3015
104call1122025-09-0116

Expected output

One row per rep_id with the Pearson correlation r between prior-week call_count and current-week win_rate for Q2 2025 weeks, excluding reps with fewer than 5 overlapping weeks. With the minimal sample data provided, no rep has at least 5 overlapping weeks, so the result set is empty.

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.

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
industryVARCHAR(50)NOT NULL
size_bucketVARCHAR(10)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idindustryregionsize_bucket
100SoftwareWestMM
101HealthcareEastSMB
102ManufacturingEastENT
103RetailCentralMM
104FinanceCentralSMB

Table reps

columntypeconstraints
rep_idINTPRIMARY KEY
hire_dateDATENOT NULL
segmentVARCHAR(20)NOT NULL
regionVARCHAR(20)NOT NULL
quota_usdDECIMAL(12,2)NOT NULL

Sample data:

hire_datequota_usdregionrep_idsegment
2024-09-01500000West10SMB
2024-12-15800000East11MM
2025-03-10400000Central12SMB

Table deals

columntypeconstraints
deal_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
created_atTIMESTAMPNOT NULL
closed_atTIMESTAMPNOT NULL
stageVARCHAR(50)NOT NULL
is_wonINTNOT NULL
amount_usdDECIMAL(12,2)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idamount_usdclosed_atcreated_atdeal_idis_wonregionrep_idstage
100120002025-04-20 09:00:002025-04-05 10:00:0011West10Closed Won
10080002025-05-10 08:00:002025-04-25 12:00:0020West10Closed Lost
10150002025-06-01 16:00:002025-05-02 11:00:0031East11Closed Won
102200002025-07-20 10:00:002025-06-15 09:00:0040East11Closed Lost
103150002025-06-22 10:00:002025-06-10 14:00:0051Central12Closed Won
10402025-07-18 12:00:002025-07-01 08:30:0060Central12Closed Lost

Table touches

columntypeconstraints
touch_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
touch_dateDATENOT NULL
channelVARCHAR(20)NOT NULL
is_primary_contactINTNOT NULL

Sample data:

account_idchannelis_primary_contactrep_idtouch_datetouch_id
100email1102025-01-101
100call1102025-04-012
100call1102025-04-013
101webinar0112025-02-154
101call1112025-04-285
102email1112025-06-166
103call1122025-06-017
104email1122025-06-298
100call1102025-08-269
101call1112025-08-2710
101call1112025-08-2711
102email1112025-08-2812
103call1122025-08-2913
103call1122025-08-3014
104call1122025-08-3015
104call1122025-09-0116

Expected output

All deals where closed_at < created_at. With the provided sample data there are no such rows, so the result is empty.

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.

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
industryVARCHAR(50)NOT NULL
size_bucketVARCHAR(10)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idindustryregionsize_bucket
100SoftwareWestMM
101HealthcareEastSMB
102ManufacturingEastENT
103RetailCentralMM
104FinanceCentralSMB

Table reps

columntypeconstraints
rep_idINTPRIMARY KEY
hire_dateDATENOT NULL
segmentVARCHAR(20)NOT NULL
regionVARCHAR(20)NOT NULL
quota_usdDECIMAL(12,2)NOT NULL

Sample data:

hire_datequota_usdregionrep_idsegment
2024-09-01500000West10SMB
2024-12-15800000East11MM
2025-03-10400000Central12SMB

Table deals

columntypeconstraints
deal_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
created_atTIMESTAMPNOT NULL
closed_atTIMESTAMPNOT NULL
stageVARCHAR(50)NOT NULL
is_wonINTNOT NULL
amount_usdDECIMAL(12,2)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idamount_usdclosed_atcreated_atdeal_idis_wonregionrep_idstage
100120002025-04-20 09:00:002025-04-05 10:00:0011West10Closed Won
10080002025-05-10 08:00:002025-04-25 12:00:0020West10Closed Lost
10150002025-06-01 16:00:002025-05-02 11:00:0031East11Closed Won
102200002025-07-20 10:00:002025-06-15 09:00:0040East11Closed Lost
103150002025-06-22 10:00:002025-06-10 14:00:0051Central12Closed Won
10402025-07-18 12:00:002025-07-01 08:30:0060Central12Closed Lost

Table touches

columntypeconstraints
touch_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
touch_dateDATENOT NULL
channelVARCHAR(20)NOT NULL
is_primary_contactINTNOT NULL

Sample data:

account_idchannelis_primary_contactrep_idtouch_datetouch_id
100email1102025-01-101
100call1102025-04-012
100call1102025-04-013
101webinar0112025-02-154
101call1112025-04-285
102email1112025-06-166
103call1122025-06-017
104email1122025-06-298
100call1102025-08-269
101call1112025-08-2710
101call1112025-08-2711
102email1112025-08-2812
103call1122025-08-2913
103call1122025-08-3014
104call1122025-08-3015
104call1122025-09-0116

Expected output

Accounts where the earliest touch_date is later than the earliest closed_at date. With the provided sample data there are no such accounts, so the result is empty.

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)

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
industryVARCHAR(50)NOT NULL
size_bucketVARCHAR(10)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idindustryregionsize_bucket
100SoftwareWestMM
101HealthcareEastSMB
102ManufacturingEastENT
103RetailCentralMM
104FinanceCentralSMB

Table reps

columntypeconstraints
rep_idINTPRIMARY KEY
hire_dateDATENOT NULL
segmentVARCHAR(20)NOT NULL
regionVARCHAR(20)NOT NULL
quota_usdDECIMAL(12,2)NOT NULL

Sample data:

hire_datequota_usdregionrep_idsegment
2024-09-01500000West10SMB
2024-12-15800000East11MM
2025-03-10400000Central12SMB

Table deals

columntypeconstraints
deal_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
created_atTIMESTAMPNOT NULL
closed_atTIMESTAMPNOT NULL
stageVARCHAR(50)NOT NULL
is_wonINTNOT NULL
amount_usdDECIMAL(12,2)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idamount_usdclosed_atcreated_atdeal_idis_wonregionrep_idstage
100120002025-04-20 09:00:002025-04-05 10:00:0011West10Closed Won
10080002025-05-10 08:00:002025-04-25 12:00:0020West10Closed Lost
10150002025-06-01 16:00:002025-05-02 11:00:0031East11Closed Won
102200002025-07-20 10:00:002025-06-15 09:00:0040East11Closed Lost
103150002025-06-22 10:00:002025-06-10 14:00:0051Central12Closed Won
10402025-07-18 12:00:002025-07-01 08:30:0060Central12Closed Lost

Table touches

columntypeconstraints
touch_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
touch_dateDATENOT NULL
channelVARCHAR(20)NOT NULL
is_primary_contactINTNOT NULL

Sample data:

account_idchannelis_primary_contactrep_idtouch_datetouch_id
100email1102025-01-101
100call1102025-04-012
100call1102025-04-013
101webinar0112025-02-154
101call1112025-04-285
102email1112025-06-166
103call1122025-06-017
104email1122025-06-298
100call1102025-08-269
101call1112025-08-2710
101call1112025-08-2711
102email1112025-08-2812
103call1122025-08-2913
103call1122025-08-3014
104call1122025-08-3015
104call1122025-09-0116

Expected output

Deals whose region does not equal their account's region. With the provided sample data, no deals violate this rule, so the result set is empty.

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

Table accounts

columntypeconstraints
account_idINTPRIMARY KEY
industryVARCHAR(50)NOT NULL
size_bucketVARCHAR(10)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idindustryregionsize_bucket
100SoftwareWestMM
101HealthcareEastSMB
102ManufacturingEastENT
103RetailCentralMM
104FinanceCentralSMB

Table reps

columntypeconstraints
rep_idINTPRIMARY KEY
hire_dateDATENOT NULL
segmentVARCHAR(20)NOT NULL
regionVARCHAR(20)NOT NULL
quota_usdDECIMAL(12,2)NOT NULL

Sample data:

hire_datequota_usdregionrep_idsegment
2024-09-01500000West10SMB
2024-12-15800000East11MM
2025-03-10400000Central12SMB

Table deals

columntypeconstraints
deal_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
created_atTIMESTAMPNOT NULL
closed_atTIMESTAMPNOT NULL
stageVARCHAR(50)NOT NULL
is_wonINTNOT NULL
amount_usdDECIMAL(12,2)NOT NULL
regionVARCHAR(20)NOT NULL

Sample data:

account_idamount_usdclosed_atcreated_atdeal_idis_wonregionrep_idstage
100120002025-04-20 09:00:002025-04-05 10:00:0011West10Closed Won
10080002025-05-10 08:00:002025-04-25 12:00:0020West10Closed Lost
10150002025-06-01 16:00:002025-05-02 11:00:0031East11Closed Won
102200002025-07-20 10:00:002025-06-15 09:00:0040East11Closed Lost
103150002025-06-22 10:00:002025-06-10 14:00:0051Central12Closed Won
10402025-07-18 12:00:002025-07-01 08:30:0060Central12Closed Lost

Table touches

columntypeconstraints
touch_idINTPRIMARY KEY
account_idINTNOT NULL
rep_idINTNOT NULL
touch_dateDATENOT NULL
channelVARCHAR(20)NOT NULL
is_primary_contactINTNOT NULL

Sample data:

account_idchannelis_primary_contactrep_idtouch_datetouch_id
100email1102025-01-101
100call1102025-04-012
100call1102025-04-013
101webinar0112025-02-154
101call1112025-04-285
102email1112025-06-166
103call1122025-06-017
104email1122025-06-298
100call1102025-08-269
101call1112025-08-2710
101call1112025-08-2711
102email1112025-08-2812
103call1122025-08-2913
103call1122025-08-3014
104call1122025-08-3015
104call1122025-09-0116

Expected output

Top 3 reps in the window 2025-08-26 to 2025-09-01 by distinct accounts touched via call (after deduplication), with ties broken by total_calls then rep_id ascending.

distinct_accounts_touchedrep_idtotal_calls
2124
1101
1111

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.