-- BAD: Comments that describe what SQL does (obvious from reading it)-- Select first name and last name from customersSELECT first_name, last_name FROM customers;
-- GOOD: Comments that explain WHY and business context-- Marketing requested VIP list for Q2 retention campaign-- VIP defined as: $5K+ lifetime spend, active in last 6 monthsSELECT 
    c.first_name ||' '|| c.last_name AS customer_name,
    c.email,
    SUM(p.amount) AS lifetime_spend,
    MAX(b.booking_date)::dateAS last_booking
FROM customers c
    INNERJOIN bookings b ON c.customer_id = b.customer_id
    INNERJOIN payments p ON b.booking_id = p.booking_id
WHERE p.payment_status ='completed'AND b.status IN ('completed', 'confirmed')
GROUPBY c.customer_id, c.first_name, c.last_name, c.email
HAVINGSUM(p.amount) >=5000ANDMAX(b.booking_date) >=CURRENT_DATE-INTERVAL'6 months'ORDERBY lifetime_spend DESC;

SELECT e.expedition_type, COUNT(DISTINCT b.booking_id),
SUM(p.amount), ROUND(AVG(p.amount), 2),
COUNT(DISTINCT b.booking_id) FILTER (WHERE b.status = 'cancelled') * 100.0 / COUNT(DISTINCT b.booking_id)
FROM expeditions e INNER JOIN expedition_instances ei
ON e.expedition_id = ei.expedition_id INNER JOIN bookings b
ON ei.instance_id = b.instance_id INNER JOIN payments p
ON b.booking_id = p.booking_id WHERE p.payment_status = 'completed'
GROUP BY e.expedition_type ORDER BY SUM(p.amount) DESC;

-- Expedition category performance analysis
-- Requested by: Marcus (Operations)
-- Purpose: Q2 planning - identify which categories to expand
-- Step 1: Calculate revenue and volume per category
WITH category_performance AS (
    SELECT 
        e.expedition_type,
        COUNT(DISTINCT b.booking_id) AS total_bookings,
        SUM(p.amount) AS total_revenue,
        ROUND(AVG(p.amount), 2) AS avg_booking_value
    FROM expeditions e
        INNER JOIN expedition_instances ei ON e.expedition_id = ei.expedition_id
        INNER JOIN bookings b ON ei.instance_id = b.instance_id
        INNER JOIN payments p ON b.booking_id = p.booking_id
    WHERE p.payment_status = 'completed'
    GROUP BY e.expedition_type
),
-- Step 2: Calculate cancellation rates per category
category_cancellations AS (
    SELECT 
        e.expedition_type,
        ROUND(
            COUNT(*) FILTER (WHERE b.status = 'cancelled') * 100.0 / COUNT(*),
            1
        ) AS cancellation_rate
    FROM expeditions e
        INNER JOIN expedition_instances ei ON e.expedition_id = ei.expedition_id
        INNER JOIN bookings b ON ei.instance_id = b.instance_id
    GROUP BY e.expedition_type
)
-- Step 3: Combine for complete picture
SELECT 
    cp.expedition_type,
    cp.total_bookings,
    cp.total_revenue,
    cp.avg_booking_value,
    cc.cancellation_rate
FROM category_performance cp
    INNER JOIN category_cancellations cc ON cp.expedition_type = cc.expedition_type
ORDER BY cp.total_revenue DESC;

SELECT 
    cnt_bid,
    sum_amt,
    avg_val,
    exp_typ
FROM ...

SELECT 
    total_bookings,
    total_revenue,
    avg_booking_value,
    expedition_type
FROM ...

-- ============================================
-- Analysis: [What question this answers]
-- Requested by: [Who asked for it]
-- Date: [When you wrote it]
-- Data source: [Which database/tables]
-- Assumptions: [Any filters or business rules]
-- ============================================
-- Step 1: [Description of first step]
WITH step_one AS (
    SELECT ...
),
-- Step 2: [Description of second step]
step_two AS (
    SELECT ...
)
-- Final output: [What the results show]
SELECT ...
FROM step_one
    JOIN step_two ON ...
ORDER BY ...;