Sales pipeline: win rates, cycles and quota
Build the numbers a sales leader runs the team on (open and weighted pipeline, win rates, sales cycles, quota attainment, loss reasons and stale deals) without counting open deals as losses or inflating revenue through joins.
Start course→ 02
→ 03
Your exercises
01The head of sales opens every forecast call with the open pipeline. A deal is open when its stage is 'prospecting', 'proposal' or 'negotiation'; 'won' and 'lost' deals are closed. Return stage, open_deals (number of deals) and pipeline_value (sum of amount) for the three open stages. Sort in funnel order: prospecting, proposal, negotiation.
Sales leadership is reviewing rep performance. For each rep, return name, won (deals with stage 'won'), lost (deals with stage 'lost') and win_rate_pct = won ÷ (won + lost) × 100 rounded to 1 decimal. Open deals are neither wins nor losses and must not be in the denominator. Sort by win_rate_pct from highest to lowest, then name A to Z.
Planning needs the typical sales cycle for each team to time hiring and quotas. For deals with stage 'won', the cycle is the number of days from created_at to closed_at. Return team, won_deals and avg_cycle_days (average cycle rounded to 1 decimal). Sort by avg_cycle_days from shortest to longest.
The VP of sales needs Q3 2026 attainment for all eight reps. Q3 won revenue is the sum of amount over deals with stage 'won' and closed_at from 2026-07-01 to 2026-09-30, by closing date rather than creation date. Return name, quarterly_quota, q3_won (0 when the rep closed nothing) and attainment_pct = q3_won ÷ quarterly_quota × 100 rounded to 1 decimal. Sort by attainment_pct from highest to lowest, then name A to Z.
Product and pricing want to see why deals are lost. For deals with stage 'lost', return lost_reason, lost_deals (count), lost_amount (sum of amount) and share_of_lost_pct = lost_deals ÷ all lost deals × 100 rounded to 1 decimal. The denominator is lost deals only, not all deals. Sort by lost_deals from highest to lowest, then lost_reason A to Z.
Today is 2026-09-30 and managers want open deals (stage 'prospecting', 'proposal' or 'negotiation') with no logged activity in the last 21 days, meaning no activity_date from 2026-09-10 to 2026-09-30. Return deal_id, company, rep (the rep's name), amount and last_activity_date (the latest activity_date for the deal, NULL if it has none). Sort by last_activity_date from oldest to newest, then deal_id.
Enablement wants to compare effort on won and lost deals. For each closed stage ('won' and 'lost'), return stage, deals (number of deals), total_amount (sum of deal amount, each deal counted once) and avg_activities (average number of logged activities per deal, rounded to 2 decimals). Sort by stage A to Z.
For the forecast, weight each open deal's amount by stage probability: prospecting 10%, proposal 40%, negotiation 70%. For all eight reps, return name, open_deals (0 if none), weighted_pipeline (sum of amount × probability over open deals, rounded to 2 decimals, 0 if none) and pipeline_rank: RANK() by weighted_pipeline, highest first, so ties share a rank. Sort by pipeline_rank, then name A to Z.