Business Problem

HubSpot's VP of Sales wants a mid-quarter pulse check on every rep and territory before the next forecast call with the board. Compare each rep's actual closed revenue against their monthly quota, calculate an achievement percentage, rank reps within their territory, and flag anyone significantly behind pace with a coaching recommendation — so sales leadership can intervene on underperforming reps and publicly recognize the ones crushing their numbers before the quarter closes.

Dataset & Schema

Sales reps with rep_id, territory, monthly_quota; deals with deal_id, rep_id, deal_amount, deal_close_date, deal_stage (Won/Lost/Open).

CREATE TABLE sales_reps (
  rep_id INTEGER PRIMARY KEY,
  rep_name TEXT,
  territory TEXT,
  monthly_quota DECIMAL(10,2)
);
CREATE TABLE deals (
  deal_id INTEGER PRIMARY KEY,
  rep_id INTEGER,
  deal_amount DECIMAL(10,2),
  deal_close_date DATE,
  deal_stage TEXT,
  FOREIGN KEY(rep_id) REFERENCES sales_reps(rep_id)
);

SQL Solution

WITH monthly_sales AS (
  SELECT 
    sr.rep_id,
    sr.rep_name,
    sr.territory,
    sr.monthly_quota,
    COUNT(DISTINCT d.deal_id) as deals_closed,
    ROUND(SUM(CASE WHEN d.deal_stage = 'Won' THEN d.deal_amount ELSE 0 END), 2) as actual_revenue,
    ROUND(SUM(CASE WHEN d.deal_stage = 'Won' THEN d.deal_amount ELSE 0 END) / sr.monthly_quota * 100, 1) as quota_achievement_pct
  FROM sales_reps sr
  LEFT JOIN deals d ON sr.rep_id = d.rep_id 
    AND STRFTIME('%Y-%m', d.deal_close_date) = '2024-03'
  GROUP BY sr.rep_id, sr.rep_name, sr.territory, sr.monthly_quota
),
performance_ranking AS (
  SELECT 
    rep_id,
    rep_name,
    territory,
    monthly_quota,
    deals_closed,
    actual_revenue,
    quota_achievement_pct,
    ROUND(actual_revenue - monthly_quota, 2) as amount_vs_quota,
    RANK() OVER (ORDER BY quota_achievement_pct DESC) as performance_rank,
    CASE 
      WHEN quota_achievement_pct >= 120 THEN 'Exceeding-Quota'
      WHEN quota_achievement_pct >= 100 THEN 'On-Track'
      WHEN quota_achievement_pct >= 75 THEN 'Below-Target'
      ELSE 'At-Risk'
    END as performance_status
  FROM monthly_sales
)
SELECT 
  rep_id,
  rep_name,
  territory,
  monthly_quota,
  deals_closed,
  actual_revenue,
  quota_achievement_pct,
  amount_vs_quota,
  performance_rank,
  performance_status,
  CASE 
    WHEN performance_rank <= 2 THEN 'Top-Performer'
    WHEN performance_rank >= 4 THEN 'Needs-Coaching'
    ELSE 'Consistent'
  END as coaching_recommendation
FROM performance_ranking
ORDER BY performance_rank ASC;

Explanation