Média mensal com datas ausentes
Encontre o número médio de solicitações do Evanston 311 criadas por dia para cada mês dos dados.
Desta vez, não ignore as datas sem solicitações.
Este exercício faz parte do curso
Análise Exploratória de Dados em SQL
Instruções do exercício
- Gere uma série de datas de 2016-01-01 a 2018-06-30.
- Faça um JOIN da série com uma subconsulta para contar o número de solicitações criadas por dia.
- Use
date_trunc()para obter os meses a partir dedate, que contém todas as datas, NÃO deday. - Use
coalesce()para substituir valores NULL decountpor 0. Calcule a média desse valor.
Exercício interativo prático
Experimente este exercício completando este código de exemplo.
-- generate series with all days from 2016-01-01 to 2018-06-30
WITH all_days AS
(SELECT generate_series(___,
___,
___::___) AS date),
-- Subquery to compute daily counts
daily_count AS
(SELECT date_trunc('day', date_created) AS day,
count(*) AS count
FROM evanston311
GROUP BY day)
-- Aggregate daily counts by month using date_trunc
SELECT ___ AS month,
-- Use coalesce to replace NULL count values with 0
avg(coalesce(___, ___)) AS average
FROM all_days
LEFT JOIN daily_count
-- Joining condition
ON all_days.___=daily_count.___
GROUP BY month
ORDER BY month;