बकेटिंग

SQL में बिज़नेस डेटा का विश्लेषण

Michel Semaan

Data Scientist

हिस्टोग्राम्स - पुनरावलोकन

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;

CASE एक्सप्रेशन

क्वेरी

SELECT
  CASE
    WHEN meal_price < 4 THEN 'Low-price meal'
    WHEN meal_price < 6 THEN 'Mid-price meal'
    ELSE 'High-price meal'
  END AS price_category,
  COUNT(DISTINCT meal_id)
FROM meals
GROUP BY price_category;

बकेटिंग - क्वेरी

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 'Low-revenue users'
    WHEN revenue < 300 THEN 'Mid-revenue users'
    ELSE 'High-revenue users'
  END AS revenue_group,
  COUNT(DISTINCT user_id) AS users
FROM user_revenues
GROUP BY revenue_group;

बकेटिंग - परिणाम

revenue_group       users
------------------  -----
Low-revenue users   473
Mid-revenue users   606
High-revenue users  225

हिस्टोग्राम्स बनाम बार ग्राफ़

प्रति उपयोगकर्ता ऑर्डर्स का हिस्टोग्राम

प्रति रेवेन्यू ग्रुप उपयोगकर्ताओं का बार चार्ट

बकेटिंग

SQL में बिज़नेस डेटा का विश्लेषण