content/posts/postgres-aggregates/sequential-ctes.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


-- EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
WITH product_with_reviews AS (
  SELECT
    products.id AS product_id,
    products.name AS product_name,
    COUNT(reviews.id) AS num_reviews
  FROM
    products
    LEFT JOIN reviews ON reviews.product_id = products.id
    -- WHERE
    --   products.id % 10 = 0
  GROUP BY
    products.id,
    products.name
)
SELECT
  product_with_reviews.product_id,
  product_name,
  num_reviews,
  COUNT(id) AS num_orders
FROM
  product_with_reviews
  LEFT JOIN orders ON orders.product_id = product_with_reviews.product_id
GROUP BY
  product_with_reviews.product_id,
  product_name,
  num_reviews;