CREATE TABLE deals (
    deal_id VARCHAR PRIMARY KEY,
    company_id VARCHAR,
    owner_id VARCHAR,
    stage VARCHAR,
    value DECIMAL(12, 2),
    probability INTEGER,  -- 0-100
    created_at DATE,
    close_date DATE,
    closed_at DATE,
    close_reason VARCHAR,  -- 'won', 'lost', 'churned'
    lost_reason VARCHAR,
    source VARCHAR  -- 'outbound', 'inbound', 'referral', 'partner'
);
 
CREATE TABLE stage_history (
    deal_id VARCHAR,
    from_stage VARCHAR,
    to_stage VARCHAR,
    entered_at TIMESTAMP,
    exited_at TIMESTAMP
);
 
CREATE TABLE reps (
    rep_id VARCHAR PRIMARY KEY,
    name VARCHAR,
    team VARCHAR,
    quota DECIMAL(12, 2)
);

-- Current pipeline by stage
SELECT 
    stage,
    COUNT(*) AS deals,
    SUM(value) AS pipeline_value,
    AVG(value) AS avg_deal_size,
    AVG(DATEDIFF('day', created_at, CURRENT_DATE)) AS avg_age_days,
    SUM(value) OVER () AS total_pipeline,
    ROUND(SUM(value) * 100.0 / SUM(value) OVER (), 1) AS pct_of_pipeline
FROM deals
WHERE closed_at IS NULL
ORDER BY 
    CASE stage
        WHEN 'Lead' THEN 1
        WHEN 'Qualified' THEN 2
        WHEN 'Proposal' THEN 3
        WHEN 'Negotiation' THEN 4
        ELSE 5
    END;

-- Win rate overall and by source
SELECT 
    source,
    COUNT(*) AS total_deals,
    COUNT(CASE WHEN close_reason = 'won' THEN 1 END) AS won,
    COUNT(CASE WHEN close_reason = 'lost' THEN 1 END) AS lost,
    ROUND(COUNT(CASE WHEN close_reason = 'won' THEN 1 END) * 100.0 / COUNT(*), 1) AS win_rate_pct,
    SUM(CASE WHEN close_reason = 'won' THEN value ELSE 0 END) AS revenue_won
FROM deals
WHERE closed_at IS NOT NULL
    AND closed_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY source
ORDER BY revenue_won DESC;
 
-- Win rate by stage it was lost in
SELECT 
    lost_reason,
    COUNT(*) AS deals_lost,
    SUM(value) AS value_lost,
    ROUND(AVG(DATEDIFF('day', created_at, closed_at)), 0) AS avg_days_in_cycle
FROM deals
WHERE close_reason = 'lost'
    AND closed_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY lost_reason
ORDER BY deals_lost DESC;

-- Rep leaderboard: current quarter
SELECT 
    r.name AS rep,
    r.team,
    COUNT(CASE WHEN d.close_reason = 'won' THEN 1 END) AS deals_closed,
    SUM(CASE WHEN d.close_reason = 'won' THEN d.value ELSE 0 END) AS revenue,
    r.quota,
    ROUND(SUM(CASE WHEN d.close_reason = 'won' THEN d.value ELSE 0 END) * 100.0 / r.quota, 1) AS quota_attainment_pct,
    COUNT(CASE WHEN d.closed_at IS NULL THEN 1 END) AS open_deals,
    SUM(CASE WHEN d.closed_at IS NULL THEN d.value ELSE 0 END) AS open_pipeline
FROM reps r
LEFT JOIN deals d ON d.owner_id = r.rep_id
    AND (d.closed_at IS NULL OR (
        d.closed_at >= DATE_TRUNC('quarter', CURRENT_DATE)
        AND d.closed_at < DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months'
    ))
GROUP BY r.name, r.team, r.quota
ORDER BY revenue DESC;

-- Average days in each stage
SELECT 
    from_stage AS stage,
    COUNT(*) AS transitions,
    ROUND(AVG(DATEDIFF('day', entered_at, exited_at)), 1) AS avg_days,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF('day', entered_at, exited_at)), 1) AS median_days,
    ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY DATEDIFF('day', entered_at, exited_at)), 1) AS p90_days
FROM stage_history
WHERE exited_at IS NOT NULL
    AND entered_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY from_stage
ORDER BY avg_days DESC;
 
-- Deal cycle time from create to close
SELECT 
    ROUND(AVG(DATEDIFF('day', created_at, closed_at)), 0) AS avg_days_to_close,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF('day', created_at, closed_at)), 0) AS median_days,
    MIN(DATEDIFF('day', created_at, closed_at)) AS fastest,
    MAX(DATEDIFF('day', created_at, closed_at)) AS slowest
FROM deals
WHERE close_reason = 'won'
    AND closed_at >= CURRENT_DATE - INTERVAL '90 days';

-- Bottom-up forecast: probability-weighted pipeline
SELECT 
    DATE_TRUNC('month', close_date) AS forecast_month,
    COUNT(*) AS deals_expected,
    SUM(value) AS total_pipeline,
    SUM(value * probability / 100.0) AS weighted_forecast,
    SUM(CASE WHEN probability >= 75 THEN value ELSE 0 END) AS commit_forecast
FROM deals
WHERE closed_at IS NULL
    AND close_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '3 months'
GROUP BY forecast_month
ORDER BY forecast_month;
 
-- Forecast accuracy: how accurate were last quarter's forecasts?
WITH quarterly_forecast AS (
    SELECT 
        DATE_TRUNC('month', closed_at) AS month,
        SUM(CASE WHEN close_reason = 'won' THEN value ELSE 0 END) AS actual_revenue
    FROM deals
    WHERE closed_at >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
        AND closed_at < DATE_TRUNC('quarter', CURRENT_DATE)
    GROUP BY month
)
SELECT 
    month,
    actual_revenue,
    LAG(actual_revenue) OVER (ORDER BY month) AS prior_month_actual,
    ROUND((actual_revenue - LAG(actual_revenue) OVER (ORDER BY month)) / 
          NULLIF(LAG(actual_revenue) OVER (ORDER BY month), 0) * 100, 1) AS mom_growth_pct
FROM quarterly_forecast
ORDER BY month;

-- At-risk deals: stalled in a stage too long
SELECT 
    d.deal_id,
    d.company_id,
    d.stage,
    d.value,
    d.owner_id,
    DATEDIFF('day', sh.entered_at, CURRENT_TIMESTAMP) AS days_in_stage,
    sh.entered_at AS stage_entered_at
FROM deals d
JOIN stage_history sh ON d.deal_id = sh.deal_id AND sh.exited_at IS NULL
WHERE d.closed_at IS NULL
    AND (
        (d.stage = 'Qualified' AND DATEDIFF('day', sh.entered_at, CURRENT_TIMESTAMP) > 14)
        OR (d.stage = 'Proposal' AND DATEDIFF('day', sh.entered_at, CURRENT_TIMESTAMP) > 21)
        OR (d.stage = 'Negotiation' AND DATEDIFF('day', sh.entered_at, CURRENT_TIMESTAMP) > 30)
    )
ORDER BY days_in_stage DESC;

-- DenchClaw pipeline query
SELECT 
    "Stage",
    COUNT(*) AS deals,
    SUM("Deal Value") AS pipeline_value,
    AVG("Deal Value") AS avg_size
FROM v_deals
WHERE "Status" = 'Active'
GROUP BY "Stage"
ORDER BY pipeline_value DESC;