What is going on?
Table:
| country_region |
date_20 |
new_cases_20 |
date_21 |
confirmed_21 |
new_cases_21 |
| Hungary |
2020-09-02 |
365 |
2021-09-02 |
812793 |
262 |
| Hungary |
2020-09-03 |
301 |
2021-09-03 |
813040 |
247 |
| Hungary |
2020-09-06 |
495 |
2021-09-06 |
813688 |
648 |
| Hungary |
2020-09-07 |
576 |
2021-09-07 |
813818 |
130 |
| Hungary |
2020-09-08 |
341 |
2021-09-08 |
814064 |
246 |
| Hungary |
2020-09-09 |
411 |
2021-09-09 |
814409 |
345 |
| Hungary |
2020-09-10 |
476 |
2021-09-10 |
814732 |
323 |
| Hungary |
2020-09-13 |
484 |
2021-09-13 |
815605 |
873 |
| Hungary |
2020-09-14 |
844 |
2021-09-14 |
815851 |
246 |
| Hungary |
2020-09-15 |
726 |
2021-09-15 |
816222 |
371 |
| Hungary |
2020-09-16 |
581 |
2021-09-16 |
816680 |
458 |
| Hungary |
2020-09-17 |
710 |
2021-09-17 |
817159 |
479 |
| Hungary |
2020-09-20 |
1070 |
2021-09-20 |
818231 |
1072 |
| Hungary |
2020-09-21 |
876 |
2021-09-21 |
818520 |
289 |
| Hungary |
2020-09-22 |
633 |
2021-09-22 |
819021 |
501 |
| Hungary |
2020-09-23 |
951 |
2021-09-23 |
819547 |
526 |
| Hungary |
2020-09-24 |
750 |
2021-09-24 |
820078 |
531 |
| Hungary |
2020-09-27 |
937 |
2021-09-27 |
821261 |
1183 |
| Hungary |
2020-09-28 |
702 |
2021-09-28 |
821526 |
265 |
| Hungary |
2020-09-29 |
851 |
2021-09-29 |
822072 |
546 |
| Hungary |
2020-09-30 |
894 |
2021-09-30 |
822705 |
633 |
| Hungary |
2020-10-01 |
848 |
2021-10-01 |
823384 |
679 |
| Hungary |
2020-10-04 |
858 |
2021-10-04 |
824876 |
1492 |
| Hungary |
2020-10-05 |
905 |
2021-10-05 |
825170 |
294 |
| Hungary |
2020-10-06 |
818 |
2021-10-06 |
825799 |
629 |
| Hungary |
2020-10-07 |
816 |
2021-10-07 |
826636 |
837 |
| Hungary |
2020-10-08 |
932 |
2021-10-08 |
827410 |
774 |
| Hungary |
2020-10-11 |
1068 |
2021-10-11 |
829456 |
2046 |
| Hungary |
2020-10-12 |
1173 |
2021-10-12 |
829911 |
455 |
| Hungary |
2020-10-13 |
1025 |
2021-10-13 |
830725 |
814 |
| Hungary |
2020-10-14 |
920 |
2021-10-14 |
831866 |
1141 |
| Hungary |
2020-10-15 |
950 |
2021-10-15 |
833115 |
1249 |
| Hungary |
2020-10-18 |
1474 |
2021-10-18 |
836389 |
3274 |
| Hungary |
2020-10-19 |
1478 |
2021-10-19 |
837248 |
859 |
| Hungary |
2020-10-20 |
989 |
2021-10-20 |
838916 |
1668 |
| Hungary |
2020-10-21 |
1423 |
2021-10-21 |
841277 |
2361 |
| Hungary |
2020-10-22 |
2032 |
2021-10-22 |
843825 |
2548 |
| Hungary |
2020-10-25 |
3149 |
2021-10-25 |
850513 |
6688 |
| Hungary |
2020-10-26 |
2316 |
2021-10-26 |
852214 |
1701 |
| Hungary |
2020-10-27 |
2079 |
2021-10-27 |
855339 |
3125 |
| Hungary |
2020-10-28 |
2291 |
2021-10-28 |
859378 |
4039 |
| Hungary |
2020-10-29 |
2194 |
2021-10-29 |
863419 |
4041 |
| Hungary |
2020-11-01 |
3878 |
2021-11-01 |
874630 |
11211 |
| Hungary |
2020-11-02 |
3581 |
2021-11-02 |
876497 |
1867 |
| Hungary |
2020-11-03 |
3989 |
2021-11-03 |
879092 |
2595 |
| Hungary |
2020-11-04 |
4219 |
2021-11-04 |
885360 |
6268 |
| Hungary |
2020-11-05 |
3928 |
2021-11-05 |
892164 |
6804 |
| Hungary |
2020-11-08 |
4673 |
2021-11-08 |
909998 |
17834 |
| Hungary |
2020-11-09 |
5162 |
2021-11-09 |
914586 |
4588 |
| Hungary |
2020-11-10 |
4140 |
2021-11-10 |
923020 |
8434 |
| Hungary |
2020-11-11 |
3945 |
2021-11-11 |
931414 |
8394 |
| Hungary |
2020-11-12 |
3927 |
2021-11-12 |
939784 |
8370 |
| Hungary |
2020-11-15 |
4238 |
2021-11-15 |
960844 |
21060 |
| Hungary |
2020-11-16 |
6495 |
2021-11-16 |
966167 |
5323 |
| Hungary |
2020-11-17 |
5203 |
2021-11-17 |
976432 |
10265 |
| Hungary |
2020-11-18 |
4290 |
2021-11-18 |
987199 |
10767 |
| Hungary |
2020-11-19 |
4512 |
2021-11-19 |
998488 |
11289 |
SQL:
with data_21 as (
select * from (
select country_region, date as date_21, confirmed as confirmed_21, deaths as deaths_21 ,LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day_21 ,confirmed - LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) as new_cases_21
from `bigquery-public-data.covid19_jhu_csse.summary`
WHERE upper(country_region) = 'HUNGARY' AND date between '2021-09-01'
AND (select max(date) from `bigquery-public-data.covid19_jhu_csse.summary`) )
WHERE new_cases_21 > 0 ORDER BY 2 asc ),
data_20 as (
select * from (
select country_region, date as date_20, confirmed as confirmed_20, deaths as deaths_20 ,LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day_20 ,confirmed - LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) as new_cases_20
from `bigquery-public-data.covid19_jhu_csse.summary`
WHERE upper(country_region) = 'HUNGARY' AND date between '2020-09-01' AND '2020-11-19' ) WHERE new_cases_20 > 0 ORDER BY 2 asc ) select * from data_20 A
inner join
(select date_21, confirmed_21, deaths_21, new_cases_21 from data_21) B
on B.date_21 = DATE_ADD(A.DATE_20, INTERVAL 1 YEAR)
