SELECT * без LIMIT в печень. База не вечна.

«Посчитай батенька retention по когортам M0–M3»

SELECT
  DATE_TRUNC('month', registration_date) AS cohort_month,
  COUNT(DISTINCT user_id) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

WITH cohort_dec AS (
  SELECT user_id, registration_date
  FROM users
  WHERE registration_date >= '2023-12-01'
    AND registration_date < '2024-01-01'
)
SELECT
  COUNT(*) AS cohort_size,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 1 THEN user_id END) AS day1,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 7 THEN user_id END) AS day7
FROM cohort_dec u
LEFT JOIN events e ON u.user_id = e.user_id;

«Как посчитать retention?»

«D30 упал. Почему?»

«Ну… retention снизился, потому что пользователи стали хуже возвращаться..»

SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blocked)

SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM blocked b
WHERE b.user_id = u.id
)

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
AND o.status = 'completed'

SELECT AVG(salary) FROM employees

SELECT AVG(COALESCE(salary, 0)) FROM employees

SELECT category, AVG(sales) AS avg_sales
FROM orders
WHERE avg_sales > 1000
GROUP BY category

SELECT category, AVG(sales) AS avg_sales
FROM orders
GROUP BY category
HAVING AVG(sales) > 1000