Segmentación

Análisis de datos empresariales en SQL

Michel Semaan

Data Scientist

Histogramas: repaso

WITH user_orders AS (
  SELECT
    user_id,
    COUNT(DISTINCT order_id) AS orders
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY user_id)

SELECT
  orders,
  COUNT(DISTINCT user_id) AS users
FROM user_orders
GROUP BY orders
ORDER BY orders ASC
LIMIT 5;

Expresión CASE

Consulta

SELECT
  CASE
    WHEN meal_price < 4 THEN 'Comida barata'
    WHEN meal_price < 6 THEN 'Comida media'
    ELSE 'Comida cara'
  END AS price_category,
  COUNT(DISTINCT meal_id)
FROM meals
GROUP BY price_category;

Segmentación: consulta

WITH user_revenues AS (
  SELECT
    user_id,
    SUM(meal_price * order_quantity) AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY user_id)


SELECT
  CASE
    WHEN revenue < 150 THEN 'Usuarios de bajo ingreso'
    WHEN revenue < 300 THEN 'Usuarios de ingreso medio'
    ELSE 'Usuarios de alto ingreso'
  END AS revenue_group,
  COUNT(DISTINCT user_id) AS users
FROM user_revenues
GROUP BY revenue_group;

Segmentación: resultado

revenue_group       users
------------------  -----
Usuarios de bajo ingreso   473
Usuarios de ingreso medio   606
Usuarios de alto ingreso  225

Histogramas vs. gráficos de barras

Histograma de pedidos por usuario

Usuarios por grupo de ingresos (barra)

Segmentación

Análisis de datos empresariales en SQL