BI calculations in SQL - examples

CR

SELECT ROUND(COUNT(DISTINCT orders.user_id) * 100.0 / COUNT(DISTINCT users.user_id), 1)
FROM tools_shop.users AS users
LEFT JOIN tools_shop.orders AS orders ON users.user_id = orders.user_id; 

LTV за весь период (ARPPU)

SELECT SUM(revenue) / COUNT(DISTINCT user_id) AS ltv
FROM online_store.orders;

SELECT p.channel,
       SUM(o.revenue) / COUNT(DISTINCT o.user_id) AS ltv
FROM online_store.orders o
JOIN online_store.profiles p ON o.user_id = p.user_id
GROUP BY p.channel;

ARPU

SELECT SUM(o.revenue) / COUNT(DISTINCT p.user_id) AS arpu
FROM online_store.profiles p
LEFT JOIN online_store.orders o ON o.user_id = p.user_id;

SELECT SUM(orders.total_amt) / COUNT(DISTINCT users.user_id) AS ltv
FROM tools_shop.orders AS orders
RIGHT JOIN tools_shop.users AS users ON users.user_id = orders.user_id;

Конверсия пользователей из первого заказа во второй

SELECT COUNT(so.user_id) * 100.0 / COUNT(p.user_id) AS cr
FROM online_store.profiles p
LEFT JOIN
-- пользователи, которые оформили два заказа и более
  (SELECT user_id,
          COUNT(*) AS orders_cnt
   FROM online_store.orders 
   GROUP BY user_id
   HAVING COUNT(*) >= 2) so ON p.user_id = so.user_id
WHERE payer IS TRUE; -- пользователи, которые совершали заказ

ROI Группируем затраты по дням

SELECT dt,
       SUM(costs) AS costs
FROM online_store.costs
GROUP BY dt;

получаем revenue по дням

SELECT event_dt AS dt,
       SUM(revenue) AS revenue
FROM online_store.orders
GROUP BY event_dt;

Соединяем

SELECT dc.dt,
       revenue * 100 / costs AS ROI
FROM
  (SELECT dt,
          SUM(costs) AS costs
   FROM online_store.costs 
   GROUP BY dt) dc
JOIN
  (SELECT event_dt AS dt,
          SUM(revenue) AS revenue
   FROM online_store.orders 
   GROUP BY event_dt) dr ON dc.dt = dr.dt;