🔹 1. Total Number of Leads
SELECT COUNT(*) AS total_leads
FROM leads;
What it does:
Returns the total number of lead records in your system.
Use case:
Gives a quick view of top-of-funnel activity — how many leads have entered the pipeline.
Beginner concept:
- Simple use of COUNT(*) for raw totals.
🔹 2. List All Opportunities and Their Stages
SELECT opportunity_id, opportunity_name, stage
FROM opportunities
ORDER BY opportunity_name;
What it does:
Lists all current opportunities along with their sales stage.
Use case:
Useful for audits, sales reviews, or building a basic opportunity tracker.
Beginner concepts:
- ORDER BY to keep the list readable
- Great for showing dropdown options in Power BI
🔹 3. Number of Opportunities per Sales Rep
SELECT rep_id, COUNT(*) AS opportunity_count
FROM opportunities
GROUP BY rep_id;
What it does:
Counts how many deals each sales rep is managing in the opportunities table.
Use case:
Monitor rep workload, spot imbalances in lead distribution.
Intermediate logic:
- Use JOIN if you want to show rep names (see query #5)
🔹 4. Total Revenue from Closed-Won Deals
SELECT SUM(amount) AS total_revenue
FROM opportunities
WHERE stage = ‘Closed-Won’;
What it does:
Adds up the value of all opportunities that have been successfully closed.
Use case:
Your total actual revenue metric — perfect for KPI cards.
Intermediate touchpoint:
- Introduces conditionally filtered aggregates (WHERE stage = …)
🔹 5. Top Performing Sales Reps by Revenue
SELECT s.rep_name, SUM(o.amount) AS revenue
FROM opportunities o
JOIN sales_reps s ON o.rep_id = s.rep_id
WHERE o.stage = ‘Closed-Won’
GROUP BY s.rep_name
ORDER BY revenue DESC
LIMIT 5;
What it does:
Calculates total revenue for each rep, ranks them, and returns the top 5.
Use case:
Identify your top closers for incentives, dashboards, or performance reviews.
Intermediate concepts:
- Table JOIN, GROUP BY, SUM, and LIMIT used together
- Could be filtered by time or region for more depth
🔹 6. Win Rate per Sales Rep
SELECT s.rep_name,
ROUND(SUM(CASE WHEN o.stage = ‘Closed-Won’ THEN 1 ELSE 0 END) / COUNT(*), 2) AS win_rate
FROM opportunities o
JOIN sales_reps s ON o.rep_id = s.rep_id
GROUP BY s.rep_name;
What it does:
Calculates the win rate for each sales rep as (Closed-Won deals ÷ total deals).
Use case:
A key sales efficiency metric — useful for dashboards and rep coaching.
Intermediate concepts:
- CASE WHEN inside SUM()
- Division and rounding logic
- Can be customized to exclude ‘Closed-Lost’ if needed
🔹 7. Monthly Revenue Trend
SELECT DATE_FORMAT(close_date, ‘%Y-%m’) AS month,
SUM(amount) AS monthly_revenue
FROM opportunities
WHERE stage = ‘Closed-Won’
GROUP BY month
ORDER BY month;
What it does:
Breaks down total revenue by month, based on the deal’s close date.
Use case:
Identify sales cycles, seasonal patterns, or month-over-month growth.
Intermediate concepts:
- Time-based grouping with DATE_FORMAT()
- Aggregate by time + sorting for trend charts
🔹 8. Average Deal Size by Stage
SELECT stage, ROUND(AVG(amount), 2) AS avg_deal_size
FROM opportunities
GROUP BY stage
ORDER BY avg_deal_size DESC;
What it does:
Shows the average size of deals in each stage of the pipeline.
Use case:
Understand how deal value changes throughout the sales funnel. Helps with forecasting.
Intermediate concepts:
- Use of AVG()
- Ordering by metric (not by name)
🔹 9. Opportunities with No Activity in Last 30 Days
SELECT opportunity_id, opportunity_name, last_activity_date
FROM opportunities
WHERE last_activity_date < CURDATE() – INTERVAL 30 DAY;
What it does:
Finds deals that haven’t had any recorded activity (call, note, meeting, etc.) in the last 30 days.
Use case:
Pipeline hygiene — flag stale or neglected opportunities for follow-up.
Intermediate concept:
- Date filtering
- Can be paired with alerts or reminders
🔹 10. Leads with No Opportunities Created
SELECT l.lead_id, l.lead_source, l.industry
FROM leads l
LEFT JOIN opportunities o ON l.lead_id = o.lead_id
WHERE o.opportunity_id IS NULL;
What it does:
Returns leads that never converted into an opportunity.
Use case:
Identify leads that need rep attention or marketing follow-up.
Intermediate concepts:
- LEFT JOIN + IS NULL = anti-join pattern
- Great for tracking conversion gaps