CommencerCommencer gratuitement

Pivoting user revenues by month

Next, Eve tells you that the C-level execs prefer wide tables over long ones because they're easier to scan. She prepared a sample report of user revenues by month, detailing the first 5 user IDs' revenues from June to August 2018. The execs told her to pivot the table by month. She's passed that task off to you.

Pivot the user revenues by month query so that the user ID is a row and each month from June to August 2018 is a column.

Cet exercice fait partie du cours

Analyzing Business Data in SQL

Afficher le cours

Instructions

  • Enable CROSSTAB() from tablefunc.
  • Declare the new pivot table's columns, user ID and the first three months of operation.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de code.

-- Import tablefunc
CREATE EXTENSION IF NOT EXISTS ___;

SELECT * FROM CROSSTAB($$
  SELECT
    user_id,
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    SUM(meal_price * order_quantity) :: FLOAT AS revenue
  FROM meals
  JOIN orders ON meals.meal_id = orders.meal_id
 WHERE user_id IN (0, 1, 2, 3, 4)
   AND order_date < '2018-09-01'
 GROUP BY user_id, delivr_month
 ORDER BY user_id, delivr_month;
$$)
-- Select user ID and the months from June to August 2018
AS ct (___ INT,
       ___ FLOAT,
       ___ FLOAT,
       ___ FLOAT)
ORDER BY user_id ASC;
Modifier et exécuter le code