Sales Cycle Length

-- Calculate average sales cycle length
WITH deal_cycles AS (
  SELECT 
    opportunity_id,
    deal_size,
    deal_type,
    lead_source,
    assigned_rep,
    created_date,
    closed_date,
    stage,
    EXTRACT(days FROM AGE(closed_date, created_date)) as cycle_days
  FROM opportunities
  WHERE stage IN ('Closed Won', 'Closed Lost')
    AND created_date >= '2024-01-01'
    AND closed_date IS NOT NULL
)
SELECT 
  'Overall' as segment,
  COUNT(*) as total_deals,
  ROUND(AVG(cycle_days), 1) as avg_cycle_days,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days), 1) as median_cycle_days,
  ROUND(AVG(CASE WHEN stage = 'Closed Won' THEN cycle_days END), 1) as avg_won_cycle_days,
  ROUND(AVG(CASE WHEN stage = 'Closed Lost' THEN cycle_days END), 1) as avg_lost_cycle_days,
  MIN(cycle_days) as shortest_cycle,
  MAX(cycle_days) as longest_cycle
FROM deal_cycles

UNION ALL

SELECT 
  CASE 
    WHEN deal_size < 10000 THEN 'Small (<$10K)'
    WHEN deal_size < 50000 THEN 'Medium ($10K-$50K)'
    ELSE 'Large (>$50K)'
  END as segment,
  COUNT(*) as total_deals,
  ROUND(AVG(cycle_days), 1) as avg_cycle_days,
  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days), 1) as median_cycle_days,
  ROUND(AVG(CASE WHEN stage = 'Closed Won' THEN cycle_days END), 1) as avg_won_cycle_days,
  ROUND(AVG(CASE WHEN stage = 'Closed Lost' THEN cycle_days END), 1) as avg_lost_cycle_days,
  MIN(cycle_days) as shortest_cycle,
  MAX(cycle_days) as longest_cycle
FROM deal_cycles
GROUP BY 1
ORDER BY total_deals DESC;

-- Analyze time spent in each sales stage
WITH stage_progression AS (
  SELECT 
    opportunity_id,
    stage_name,
    entered_date,
    exited_date,
    EXTRACT(days FROM AGE(exited_date, entered_date)) as days_in_stage,
    ROW_NUMBER() OVER (PARTITION BY opportunity_id ORDER BY entered_date) as stage_order
  FROM opportunity_stage_history
  WHERE entered_date >= '2024-01-01'
    AND exited_date IS NOT NULL
),
stage_averages AS (
  SELECT 
    stage_name,
    stage_order,
    COUNT(*) as opportunities,
    ROUND(AVG(days_in_stage), 1) as avg_days_in_stage,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_in_stage), 1) as median_days_in_stage,
    ROUND(STDDEV(days_in_stage), 1) as stddev_days
  FROM stage_progression
  GROUP BY 1, 2
)
SELECT 
  stage_name,
  opportunities,
  avg_days_in_stage,
  median_days_in_stage,
  stddev_days,
  ROUND(100.0 * avg_days_in_stage / SUM(avg_days_in_stage) OVER (), 1) as percent_of_total_cycle
FROM stage_averages
ORDER BY stage_order;

-- Track sales cycle trends over time
WITH monthly_cycles AS (
  SELECT 
    DATE_TRUNC('month', closed_date) as month,
    lead_source,
    COUNT(*) as deals_closed,
    ROUND(AVG(EXTRACT(days FROM AGE(closed_date, created_date))), 1) as avg_cycle_days,
    SUM(deal_size) as total_revenue
  FROM opportunities
  WHERE stage = 'Closed Won'
    AND closed_date >= '2023-01-01'
    AND created_date IS NOT NULL
  GROUP BY 1, 2
),
cycle_trends AS (
  SELECT 
    month,
    lead_source,
    deals_closed,
    avg_cycle_days,
    total_revenue,
    LAG(avg_cycle_days) OVER (PARTITION BY lead_source ORDER BY month) as prev_month_cycle,
    AVG(avg_cycle_days) OVER (
      PARTITION BY lead_source 
      ORDER BY month 
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as three_month_avg
  FROM monthly_cycles
)
SELECT 
  month,
  lead_source,
  deals_closed,
  avg_cycle_days,
  ROUND(total_revenue / deals_closed, 0) as avg_deal_size,
  ROUND(three_month_avg, 1) as three_month_avg_cycle,
  ROUND(avg_cycle_days - prev_month_cycle, 1) as month_over_month_change,
  CASE 
    WHEN prev_month_cycle > 0 THEN 
      ROUND(100.0 * (avg_cycle_days - prev_month_cycle) / prev_month_cycle, 1)
    ELSE NULL 
  END as percent_change
FROM cycle_trends
WHERE month >= CURRENT_DATE - INTERVAL '12 months'
ORDER BY month DESC, lead_source;