SQL Query Sample Code

The query below is to set up the base of the table utilizing CTEs and joins. The base can set up multiple charts.

DataFrameas

all_weeks_countries

variable

with wc as(SELECT * FROM public.all_weeks_countries),
wg as(SELECT * FROM public.all_weeks_global),
mp as (SELECT * FROM public.most_popular),

base as(
	SELECT wc.show_title,wc.country_name,wc.country_iso2, wc.category, wc.cumulative_weeks_in_top_10 as "times_in_top_ten", wc.season_title, wg.weekly_hours_viewed, mp.rank, mp.hours_viewed_first_28_days as "hours_viewed"
from wc
left join wg
on wc.show_title = wg.show_title and wc.show_title = wg.season_title
left join mp
on wc.show_title = mp.show_title and wc.show_title = mp.season_title
)

select distinct country_name, category, sum(times_in_top_ten) as times_in_top_ten
from base
group by country_name, category
order by country_name, category, times_in_top_ten DESC

This query joins utilizing CTEs. A subquery can be written, but depending on the size of the data this can slow down processing speed. CTEs can be used to optimize a query that relies heavily on subqueries.