Producing executive reports

Analyzing Business Data in SQL

Michel Semaan

Data Scientist

Readability

Dates: Use readable date formats (August 2018, not 2018-08-01)
Numbers: Round numbers to the second decimal at most (98.76, not 98.761234)
Table shape: Reshape long tables into wide ones, pivoting by date when possible
Order: Don't forget to sort!

Executive report - query

Query

SELECT
  eatery,
  TO_CHAR(order_date, 'MM-Mon YYYY') AS delivr_month,
  COUNT(DISTINCT order_id) AS count_orders
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
WHERE order_date >= '2018-10-01'
GROUP BY eatery, delivr_month
ORDER BY eatery, delivr_month;

Result

eatery               delivr_month  count_orders
-----------------  ------------  ------------
Bean Me Up Scotty  10-Oct 2018   709         
Bean Me Up Scotty  11-Nov 2018   1143        
Bean Me Up Scotty  12-Dec 2018   2168        
Burgatorio         10-Oct 2018   679         
...                ...           ...

Executive report (II)

WITH eatery_orders AS (
  SELECT
    eatery,
    TO_CHAR(order_date, 'MM-Mon YYYY') AS delivr_month,
    COUNT(DISTINCT order_id) AS count_orders
  FROM meals
  WHERE order_date >= '2018-10-01'
  JOIN orders ON meals.meal_id = orders.meal_id
  GROUP BY eatery, delivr_month)

SELECT
  eatery,
  delivr_month,
  RANK() OVER
    (PARTITION BY delivr_month
     ORDER BY count_orders DESC) :: INT AS orders_rank
FROM eatery_orders
ORDER BY eatery, delivr_month;

eatery               delivr_month  orders_rank
-----------------  ------------  -----------
Bean Me Up Scotty  10-Oct 2018     2          
Bean Me Up Scotty  11-Nov 2018     4          
Bean Me Up Scotty  12-Dec 2018   2          
Burgatorio         10-Oct 2018     4            
...                ...           ...

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
  ...
$$) AS ct (eatery TEXT,
           "10-Oct 2018" INT,
           "11-Nov 2018" INT,
           "12-Dec 2018" INT)
ORDER BY eatery ASC;

Executive report - result

eatery                  Q2 2018  Q3 2018  Q4 2018
----------------------  -------  -------  -------
The Moon Wok            1        1        1      
Bean Me Up Scotty       3        2        2      
Leaning Tower of Pizza  4        4        3      
Burgatorio              2        3        4      
Life of Pie             5        5        5

Producing executive reports

Analyzing Business Data in SQL