Open jaredquass1 opened 1 month ago
select release_year, release_half, sum(game_count) as game_count from ( select strftime('%Y', release_date) as release_year, case when strftime('%m', release_date) in ('01', '02', '03', '04', '05', '06') then 'first_half' when strftime('%m', release_date) in ('07', '08', '09', '10', '11', '12') then 'second_half' else 'unknown' end as release_half, count(*) as game_count from video_games group by release_year, strftime('%m', release_date) ) subquery group by release_year, release_half order by release_year, release_half;
-- This query is similar but it contains a subquery.
select strftime('%Y', release_date) as release_year, strftime('%m', release_date) as release_month, Case when strftime('%m', release_date) in ('01', '02', '03', '04', '05', '06') then 'first_half' when strftime('%m', release_date) in ('07', '08', '09', '10', '11', '12') then 'second_half' else 'unknown' end as release_half, count(*) as game_count from video_games group by release_year, release_month order by release_half;
-- This query allows you to see the release year, release month, the release half as it is in a case statement, and the count of the games. It's very useful as it shows you when the games release by year and month.