SQL Business Questions Analysis

SELECT
    channel,
    ROUND(SUM(revenue), 2) AS total_revenue,
    COUNT(DISTINCT order_id) AS orders
FROM orders
GROUP BY channel
ORDER BY total_revenue DESC;

SELECT
    product_category,
    ROUND(SUM(revenue), 2) AS total_revenue,
    COUNT(DISTINCT order_id) AS orders
FROM orders
GROUP BY product_category
ORDER BY total_revenue DESC;

SELECT
    a.channel,
    a.campaign,
    ROUND(SUM(o.revenue), 2) AS revenue,
    ROUND(SUM(a.spend), 2) AS spend,
    ROUND(SUM(o.revenue) / NULLIF(SUM(a.spend), 0), 2) AS roas
FROM ad_performance a
LEFT JOIN orders o
    ON a.channel = o.channel
    AND a.campaign = o.campaign
GROUP BY a.channel, a.campaign
ORDER BY roas DESC;

WITH CampaignEfficiency AS (
    SELECT 
        a.channel,
        a.campaign,
        ROUND(SUM(a.spend), 2) AS total_spend,
        ROUND(SUM(o.revenue), 2) AS total_revenue,
        ROUND(SUM(o.revenue) / NULLIF(SUM(a.spend), 0), 2) AS roas_ratio
    FROM ad_performance a
    LEFT JOIN orders o 
        ON a.channel = o.channel 
        AND a.campaign = o.campaign
    GROUP BY a.channel, a.campaign
)
SELECT 
    channel,
    campaign,
    total_spend,
    total_revenue,
    CONCAT(ROUND(roas_ratio * 100, 0), '%') AS roas
FROM CampaignEfficiency
WHERE roas_ratio < 0.50 
ORDER BY total_spend DESC;

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month, 
    ROUND(SUM(revenue), 2) AS monthly_revenue
FROM orders
GROUP BY month
ORDER BY month;

WITH CustomerStats AS (
    SELECT 
        customer_id,
        COUNT(order_id) AS total_orders,
        SUM(revenue) AS lifetime_value
    FROM orders
    GROUP BY customer_id
),
BucketedCustomers AS (
    SELECT 
        customer_id,
        lifetime_value,
        CASE 
            WHEN total_orders = 1 THEN '1 Order'
            WHEN total_orders = 2 THEN '2 Orders'
            WHEN total_orders BETWEEN 3 AND 4 THEN '3-4 Orders'
            WHEN total_orders BETWEEN 5 AND 6 THEN '5-6 Orders'
            WHEN total_orders BETWEEN 7 AND 8 THEN '7-8 Orders'
            ELSE '9+ Orders' 
        END AS order_frequency,

        CASE 
            WHEN total_orders = 1 THEN 1
            WHEN total_orders = 2 THEN 2
            WHEN total_orders BETWEEN 3 AND 4 THEN 3
            WHEN total_orders BETWEEN 5 AND 6 THEN 4
            WHEN total_orders BETWEEN 7 AND 8 THEN 5
            ELSE 6 
        END AS sort_order
    FROM CustomerStats
)
SELECT 
    order_frequency,
    COUNT(customer_id) AS total_customers,
    ROUND((COUNT(customer_id) * 100.0) / (SELECT COUNT(*) FROM CustomerStats), 1) AS pct_of_base,
    SUM(lifetime_value) AS total_ltv
FROM BucketedCustomers
GROUP BY order_frequency, sort_order
ORDER BY sort_order;

SELECT
    country,
    ROUND(AVG(revenue), 2) AS average_order_value
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
GROUP BY country
ORDER BY average_order_value DESC;