SQL

SELECT * FROM public.billing

SELECT b.customer_name, b.billing_creation_date, b.billed_amount
FROM public.billing AS b
LIMIT 5

SELECT b.customer_name, MIN(TO_CHAR(b.billing_creation_date, 'YYYY')), MAX(TO_CHAR(b.billing_creation_date, 'YYYY')), SUM(b.billed_amount) AS total_billed
FROM public.billing AS b
GROUP BY customer_name
ORDER BY total_billed DESC
LIMIT 5;

SELECT b.customer_name AS name, b.billing_creation_date AS date, SUM(b.billed_amount)
FROM public.billing AS b
-- ROLLUP compute subtotal 
GROUP BY ROLLUP (name, date)
LIMIT 5

SELECT *
FROM public.movie_title_cat

SELECT DISTINCT(category)
FROM public.movie_title_cat

SELECT category
FROM public.movie_title_cat
WHERE category LIKE 'Films%'

SELECT *
FROM public.all_weeks_global

SELECT COUNT(show_title) AS total, COUNT(DISTINCT(show_title)) AS distinct
FROM public.all_weeks_global

SELECT EXTRACT(MONTH FROM week::date) AS month, SUM(weekly_hours_viewed) AS sum_weekly_hours_viewed
FROM public.all_weeks_global
GROUP BY month
ORDER BY sum_weekly_hours_viewed DESC

SELECT 
	DISTINCT(show_title),
	SUM(weekly_hours_viewed) OVER(PARTITION BY show_title) AS weekly_hours_viewed_by_show_title
FROM public.all_weeks_global
GROUP BY show_title, weekly_hours_viewed
ORDER BY weekly_hours_viewed_by_show_title DESC

WITH t1 AS (
SELECT 
	DISTINCT(show_title),
	SUM(weekly_hours_viewed) OVER(PARTITION BY show_title) AS hours_viewed_by_show
FROM public.all_weeks_global
GROUP BY show_title, weekly_hours_viewed
ORDER BY hours_viewed_by_show DESC)

SELECT show_title, RANK() OVER(ORDER BY hours_viewed_by_show DESC)
FROM t1

SELECT *
FROM product_emissions

SELECT DISTINCT(p.company), p.industry_group, ROUND(avg_pcf.carbon_pcf, 2) AS avg_carbom_pcf
FROM product_emissions AS p, (SELECT industry_group, AVG(carbon_footprint_pcf) AS carbon_pcf
							  FROM product_emissions
							  GROUP BY industry_group) AS avg_pcf
WHERE p.industry_group = avg_pcf.industry_group
AND p.carbon_footprint_pcf > avg_pcf.carbon_pcf
ORDER BY avg_carbom_pcf DESC