Oppiskele Combining Multiple Analytical Techniques

Pyyhkäise näyttääksesi valikon

Combining multiple analytical techniques in SQL is essential for creating rich, actionable business reports that go far beyond simple aggregations. When you integrate window functions, Common Table Expressions (CTEs), and advanced aggregations, you can answer complex business questions in a single query. This approach is especially valuable for executive dashboards, where you need to display metrics such as rankings, growth rates, and retention insights together. By chaining together CTEs for intermediate calculations, using window functions for ranking or running totals, and leveraging aggregation for summarization, you create a flexible pipeline that can adapt to nearly any analytical requirement.

Note

Example:
Suppose you want to analyze customer cohorts, calculate running totals of revenue, and identify the Top-N products by sales in one report. You can use CTEs to define each step:

The first CTE groups users by cohort (e.g., signup month);
The second CTE aggregates revenue per period and computes running totals with SUM(...) OVER (ORDER BY ...);
The third CTE ranks products by total revenue using ROW_NUMBER() or RANK() window functions;
The final SELECT joins these results, presenting a unified view for business analysis.


              12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
            
-- Cohort analysis, running totals, and Top-N products in one query
WITH cohort AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', created_at) AS signup_month
  FROM customers
),
monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', o.order_date) AS month,
    SUM(oi.price * oi.quantity) AS revenue
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY 1
),
running_totals AS (
  SELECT
    month,
    revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_revenue
  FROM monthly_revenue
),
product_sales AS (
  SELECT
    oi.product_id,
    p.name,
    SUM(oi.price * oi.quantity) AS total_sales
  FROM order_items oi
  JOIN products p ON oi.product_id = p.product_id
  GROUP BY oi.product_id, p.name
),
top_products AS (
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY total_sales DESC) AS sales_rank
  FROM product_sales
  WHERE total_sales > 0
)
SELECT
  t.name AS top_product,
  t.total_sales,
  r.month,
  r.running_revenue,
  c.signup_month
FROM top_products t
CROSS JOIN running_totals r
LEFT JOIN cohort c ON c.customer_id = 1 -- example join for illustration
WHERE t.sales_rank <= 5
ORDER BY t.sales_rank, r.month;

Consider this real-world use case:
The executive team wants a dashboard showing the top 5 products by revenue, their month-over-month (MoM) growth, and retention rates.
To deliver this, you need to combine several analytical SQL techniques:

Use CTEs to break down the problem: one for product sales, one for calculating MoM growth, and one for retention cohort analysis;
Apply window functions to rank products and compute growth rates;
Aggregate data to summarize revenue and count retained customers;
Join these CTEs in the final SELECT to create a single, comprehensive report.


              12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667
            
-- Top 5 products by revenue, MoM growth, and retention rates
WITH product_monthly_sales AS (
  SELECT
    oi.product_id,
    p.name AS product_name,
    DATE_TRUNC('month', o.order_date) AS sales_month,
    SUM(oi.price * oi.quantity) AS monthly_revenue
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.order_id
  JOIN products p ON oi.product_id = p.product_id
  GROUP BY oi.product_id, p.name, sales_month
),
product_mom_growth AS (
  SELECT
    product_id,
    product_name,
    sales_month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY sales_month) AS prev_month_revenue,
    CASE
      WHEN LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY sales_month) IS NULL THEN NULL
      WHEN LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY sales_month) = 0 THEN NULL
      ELSE ROUND(100.0 * (monthly_revenue - LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY sales_month)) / NULLIF(LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY sales_month),0),2)
    END AS mom_growth_pct
  FROM product_monthly_sales
),
top_products AS (
  SELECT
    product_id,
    product_name,
    SUM(monthly_revenue) AS total_revenue
  FROM product_monthly_sales
  GROUP BY product_id, product_name
  ORDER BY total_revenue DESC
  LIMIT 5
),
retention_cohort AS (
  SELECT
    c.customer_id,
    DATE_TRUNC('month', c.created_at) AS cohort_month,
    DATE_TRUNC('month', o.order_date) AS order_month,
    COUNT(DISTINCT o.order_id) AS orders_in_month
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, cohort_month, order_month
),
retention_rates AS (
  SELECT
    cohort_month,
    order_month,
    COUNT(DISTINCT customer_id) AS retained_customers
  FROM retention_cohort
  GROUP BY cohort_month, order_month
)
SELECT
  t.product_name,
  t.total_revenue,
  p.sales_month,
  p.monthly_revenue,
  p.mom_growth_pct,
  r.cohort_month,
  r.order_month,
  r.retained_customers
FROM top_products t
LEFT JOIN product_mom_growth p ON t.product_id = p.product_id
LEFT JOIN retention_rates r ON r.order_month = p.sales_month
ORDER BY t.total_revenue DESC, p.sales_month;

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 1. Luku 18

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Osio 1. Luku 18

Combining Multiple Analytical Techniques

1. Why is combining Common Table Expressions (CTEs) with window functions valuable for analytical SQL queries?

2. Which approach allows you to calculate MoM growth for product revenue using window functions, while avoiding division by zero or NULL values?

3. What is the main benefit of joining retention cohort analysis with other analytical results, such as product rankings and revenue growth, in a single SQL report?