SQL window function / running total

 
  • Apr 10, 2020
  • 0 views

This is a inner join. We will need to add two conditions. one to get each day record and all next days records. 

DROP TABLE IF EXISTS `nytimes_covid_counties_day_seq`;

CREATE TABLE `nytimes_covid_counties_day_seq`
AS
SELECT a.state, a.`date`, a.cases, COUNT(*) seq
  FROM nytimes_covid_states a, nytimes_covid_states b
 WHERE a.state = b.state
   AND a.`date` >= b.`date`
 GROUP BY a.state, a.`date`, a.cases

Another way of doing this is using the window functions.

DROP TABLE IF EXISTS `nytimes_covid_states_day_seq`;
CREATE TABLE `nytimes_covid_states_day_seq`
AS
SELECT *, RANK() OVER( PARTITION BY `state` ORDER BY `date`) AS seq
FROM `nytimes_covid_states`;

window_sql_output