Summarizing Data in SQL

basic_query.csv

basic_query.sql

SELECT
	date,
	count(*) as num_customers,
	count(distinct user_id) as distinct_customers,
	sum(bananas_sold) as total_bananas,
	sum(revenue) as total_revenue,
	avg(revenue) as revenue_per_sale
FROM banana_sales
GROUP BY date
ORDER BY date;

histogram.csv

histogram.sql

select 
	floor(revenue/5.00)*5 as bucket_floor,
	count(*) as count
from banana_sales
group by 1
order by 1;

histogram_label.csv

histogram_label.sql

select
    bucket_floor,
    CONCAT(bucket_floor, ' to ', bucket_ceiling) as bucket_name,
    count(*) as count
from (
	select 
		floor(revenue/5.00)*5 as bucket_floor,
		floor(revenue/5.00)*5 + 5 as bucket_ceiling
	from web_sessions_table
) a
group by 1, 2
order by 1;

histogram_naive.sql

select
	revenue,
	count(*)
from banana_sales
group by revenue
order by revenue;

joint.csv

joint.sql

select 
	floor(wait_time/10.00)*10 as wait_time_bucket,
	avg(revenue) as avg_revenue
from banana_sales
group by 1
order by 1;

joint2.sql

select
	corr(wait_time, revenue) as correlation,
	covar_samp(wait_time, revenue) as covariance
from banana_sales;

percentiles.csv

percentiles.sql

SELECT
	date,
	percentile_cont (0.25) WITHIN GROUP
		(ORDER BY wait_time ASC) OVER(PARTITION BY date) as percentile_25,
	percentile_cont (0.50) WITHIN GROUP
		(ORDER BY wait_time ASC) OVER(PARTITION BY date) as percentile_50,
	percentile_cont (0.75) WITHIN GROUP
		(ORDER BY wait_time ASC) OVER(PARTITION BY date) as percentile_75,
	avg(wait_time) as avg -- for comparison
FROM banana_sales
GROUP BY date
ORDER BY date;

percentiles2.csv

percentiles2.sql

SELECT
	t1.date,
	t1.wait_time as median
FROM (	
	SELECT
		date,
		wait_time,
		ROW_NUMBER() OVER(ORDER BY wait_time PARTITION BY date) as row_num
	FROM banana_sales
) t
JOIN (
	SELECT
		date,
		count(*) as total
	FROM banana_sales
	GROUP BY date
) t2
	ON
		t1.date = t2.date
-- for simplicity, we take a simple solution when the list has an even length, to just choose one value
WHERE t1.row_num =
  CASE when t2.total % 2 = 0 
		THEN t2.total / 2
		ELSE (t2.total + 1) / 2
	END;