Product & Business Growth Analysis Using SQL

SELECT
    YEAR(ws.created_at)     AS year,
    QUARTER(ws.created_at)  AS quarter,
    COUNT(DISTINCT ws.website_session_id) AS sessions,
    COUNT(DISTINCT o.order_id)           AS orders
FROM website_sessions ws
LEFT JOIN orders o
    USING (website_session_id)
GROUP BY 1, 2;

SELECT
    YEAR(ws.created_at)    AS year,
    QUARTER(ws.created_at) AS quarter,
    COUNT(DISTINCT ws.website_session_id) AS sessions,
    COUNT(DISTINCT o.order_id)           AS orders,
    SUM(o.price_usd)                     AS revenue,
    COUNT(DISTINCT o.order_id)
        / COUNT(DISTINCT ws.website_session_id) AS session_to_order_cvr,
    SUM(o.price_usd)
        / COUNT(DISTINCT o.order_id)           AS rev_per_order,
    SUM(o.price_usd)
        / COUNT(DISTINCT ws.website_session_id) AS rev_per_session
FROM website_sessions ws
LEFT JOIN orders o
    USING (website_session_id)
GROUP BY 1, 2;

SELECT
    YEAR(ws.created_at)    AS year,
    QUARTER(ws.created_at) AS quarter,
    COUNT(DISTINCT CASE WHEN utm_source = 'gsearch'
        AND utm_campaign = 'nonbrand'
        THEN o.order_id END)               AS gsearch_nonbrand,
    COUNT(DISTINCT CASE WHEN utm_source = 'bsearch'
        AND utm_campaign = 'nonbrand'
        THEN o.order_id END)               AS bsearch_nonbrand,
    COUNT(DISTINCT CASE WHEN utm_campaign = 'brand'
        THEN o.order_id END)               AS brand_orders,
    COUNT(DISTINCT CASE WHEN utm_source IS NULL
        AND http_referer IS NULL
        THEN o.order_id END)               AS direct_type_in,
    COUNT(DISTINCT CASE WHEN utm_source IS NULL
        AND http_referer IS NOT NULL
        THEN o.order_id END)               AS organic_search
FROM website_sessions ws
LEFT JOIN orders o
    USING (website_session_id)
GROUP BY 1, 2;

SELECT
    YEAR(ws.created_at)    AS year,
    QUARTER(ws.created_at) AS quarter,
    COUNT(DISTINCT CASE WHEN utm_source = 'gsearch'
        AND utm_campaign = 'nonbrand'
        THEN ws.website_session_id END)
    / COUNT(DISTINCT CASE WHEN utm_source = 'gsearch'
        AND utm_campaign = 'nonbrand'
        THEN o.order_id END)               AS gsearch_sessions_per_order,
    -- (repeat pattern for bsearch, brand, direct, organic)
    COUNT(DISTINCT CASE WHEN utm_source IS NULL
        AND http_referer IS NOT NULL
        THEN ws.website_session_id END)
    / COUNT(DISTINCT CASE WHEN utm_source IS NULL
        AND http_referer IS NOT NULL
        THEN o.order_id END)               AS organic_sessions_per_order
FROM website_sessions ws
LEFT JOIN orders o USING (website_session_id)
GROUP BY 1, 2;

SELECT
    YEAR(created_at)  AS year,
    MONTH(created_at) AS month,
    SUM(CASE WHEN product_id = 1
        THEN price_usd END)           AS mr_fuzzy_rev,
    SUM(CASE WHEN product_id = 1
        THEN price_usd - cogs_usd END) AS mr_fuzzy_margin,
    SUM(CASE WHEN product_id = 2
        THEN price_usd END)           AS love_bear_rev,
    SUM(CASE WHEN product_id = 2
        THEN price_usd - cogs_usd END) AS love_bear_margin,
    -- repeat for products 3 and 4
    SUM(price_usd)  AS total_rev,
    SUM(cogs_usd)   AS total_cogs
FROM order_items
GROUP BY 1, 2
ORDER BY 1, 2;

WITH ms_product_pg AS (
    SELECT
        website_session_id,
        website_pageview_id,
        created_at AS product_pg_seen_at
    FROM website_pageviews
    WHERE pageview_url = '/products'
)
SELECT
    YEAR(product_pg_seen_at)   AS year,
    MONTH(product_pg_seen_at)  AS month,
    COUNT(DISTINCT ms.website_session_id)  AS sessions_to_products,
    COUNT(DISTINCT wp.website_session_id)  AS clicked_next_pg,
    COUNT(DISTINCT wp.website_session_id)
        / COUNT(DISTINCT ms.website_session_id) AS clickthrough_rate,
    COUNT(DISTINCT o.order_id)              AS orders,
    COUNT(DISTINCT o.order_id)
        / COUNT(DISTINCT ms.website_session_id) AS products_to_order_rt
FROM ms_product_pg ms
LEFT JOIN website_pageviews wp
    ON ms.website_session_id = wp.website_session_id
    AND ms.website_pageview_id < wp.website_pageview_id
LEFT JOIN orders o
    ON o.website_session_id = ms.website_session_id
GROUP BY 1, 2;

CREATE TEMPORARY TABLE primary_products
SELECT
    order_id,
    primary_product_id,
    created_at AS ordered_at
FROM orders
WHERE created_at > '2014-12-05';

SELECT
    primary_product_id,
    COUNT(DISTINCT order_id)                          AS total_orders,
    COUNT(DISTINCT CASE WHEN cross_sell_product_id = 1
        THEN order_id END)                             AS xsold_p1,
    COUNT(DISTINCT CASE WHEN cross_sell_product_id = 4
        THEN order_id END)                             AS xsold_p4,
    COUNT(DISTINCT CASE WHEN cross_sell_product_id = 4
        THEN order_id END)
        / COUNT(DISTINCT order_id)                     AS xsold_p4_rt
FROM (
    SELECT pp.*, oi.product_id AS cross_sell_product_id
    FROM primary_products pp
    LEFT JOIN order_items oi
        ON oi.order_id = pp.order_id
        AND oi.is_primary_item = 0
) AS pp_w_xsell
GROUP BY 1;