Subqueries and CTEs

WITH top_customers AS (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM myschema.orders
  GROUP BY customer_id
  HAVING SUM(total) > 1000
)
SELECT c.name, tc.lifetime_value
FROM myschema.customers AS c
INNER JOIN top_customers AS tc ON c.id = tc.customer_id
ORDER BY tc.lifetime_value

WITH status_labels AS (
  SELECT * FROM (VALUES
    ('pending', 'Awaiting payment'),
    ('shipped', 'On the way'),
    ('delivered', 'Complete')
  ) AS t(code, label)
)
SELECT o.id, sl.label
FROM myschema.orders AS o
INNER JOIN status_labels AS sl ON o.status = sl.code

WITH "sales-data" ("order-id", "Safety.Warning") AS VALUES (
  (1, 'green'),
  (2, 'yellow')
)
SELECT "order-id", "Safety.Warning"
FROM "sales-data"
ORDER BY "order-id"

WITH
  active_orders AS (
    SELECT * FROM myschema.orders WHERE status != 'cancelled'
  ),
  order_totals AS (
    SELECT customer_id, COUNT(*) AS cnt, SUM(total) AS total
    FROM active_orders
    GROUP BY customer_id
  )
SELECT c.name, ot.cnt, ot.total
FROM myschema.customers AS c
INNER JOIN order_totals AS ot ON c.id = ot.customer_id
WHERE ot.cnt >= 3

SELECT * FROM myschema.orders
WHERE total > (SELECT AVG(total) FROM myschema.orders)

-- Orders where the total exceeds the customer's average order total
SELECT o.id, o.customer_id, o.total
FROM myschema.orders AS o
WHERE o.total > (
  SELECT AVG(o2.total)
  FROM myschema.orders AS o2
  WHERE o2.customer_id = o.customer_id
)

SELECT sub.customer_id, sub.order_count
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM myschema.orders
  GROUP BY customer_id
) AS sub
WHERE sub.order_count > 10

SELECT
  o.id,
  o.total,
  (SELECT AVG(total) FROM myschema.orders) AS avg_total
FROM myschema.orders AS o

SELECT
  c.name,
  (SELECT COUNT(*) FROM myschema.orders AS o WHERE o.customer_id = c.id) AS order_count
FROM myschema.customers AS c

SELECT customer_id, COUNT(*) AS order_count
FROM myschema.orders
GROUP BY customer_id
HAVING COUNT(*) > (SELECT AVG(cnt) FROM (
  SELECT COUNT(*) AS cnt FROM myschema.orders GROUP BY customer_id
) AS t)

SELECT name FROM myschema.customers AS c
ORDER BY (SELECT COUNT(*) FROM myschema.orders AS o WHERE o.customer_id = c.id) DESC