Criando relatórios executivos

Análise de Dados de Negócios em SQL

Michel Semaan

Data Scientist

Legibilidade

Datas: Use formatos legíveis (August 2018, não 2018-08-01)
Números: Arredonde no máx. para 2 casas decimais (98.76, não 98.761234)
Formato da tabela: Converta tabelas longas em largas, pivotando por data quando possível
Ordem: Não esqueça de ordenar!

Relatório executivo - consulta

Consulta

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;

Resultado

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         
...                ...           ...

Relatório executivo (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;

Relatório executivo - resultado

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

Criando relatórios executivos

Análise de Dados de Negócios em SQL