Raggruppamento per fasce

Analizzare i dati aziendali in SQL

Michel Semaan

Data Scientist

Istogrammi - ripasso

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;

Espressione CASE

Query

SELECT
  CASE
    WHEN meal_price < 4 THEN 'Pasto a basso prezzo'
    WHEN meal_price < 6 THEN 'Pasto a prezzo medio'
    ELSE 'Pasto a prezzo alto'
  END AS price_category,
  COUNT(DISTINCT meal_id)
FROM meals
GROUP BY price_category;

Raggruppamento per fasce - query

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 'Utenti a basso ricavo'
    WHEN revenue < 300 THEN 'Utenti a medio ricavo'
    ELSE 'Utenti ad alto ricavo'
  END AS revenue_group,
  COUNT(DISTINCT user_id) AS users
FROM user_revenues
GROUP BY revenue_group;

Raggruppamento per fasce - risultato

revenue_group        users
------------------  -----
Utenti a basso ricavo   473
Utenti a medio ricavo   606
Utenti ad alto ricavo   225

Istogrammi vs grafici a barre

Istogramma degli ordini per utente

Utenti per fascia di ricavo - grafico a barre

Raggruppamento per fasce

Analizzare i dati aziendali in SQL