---- Frequently Bought Together
with sessionsWherePurchased (user_session_id) as (
	select user_session_id 
		from events 
		where event_type = 'purchase' and product_id= $1
)
select
	categories.category_code, 
	p.price, 
	p.id,
	brands.brand
from products p
inner join events e on e.product_id = p.id
inner join sessionsWherePurchased on e.user_session_id = sessionsWherePurchased.user_session_id and e.event_type = 'purchase' and e.product_id <> $1
inner join categories
	on p.category_id=categories.id
inner join brands
	on p.brand_id = brands.id
group by 3, 1, 2,4 order by count(*) desc limit $2;
--28720715 apparel.shoes



---- Similar Items
with 
sessionsWherePurchased (user_session_id) as (
	select user_session_id
		from events 
		where event_type = 'purchase' and product_id = $1
),
frequentlyBoughtTogether as (
	select
	categories.category_code, 
	p.price, 
	p.id,
	brands.brand
	from products p
	inner join events e on e.product_id = p.id
	inner join sessionsWherePurchased on e.user_session_id = sessionsWherePurchased.user_session_id and e.event_type = 'purchase' and e.product_id <> $1
	inner join categories
		on p.category_id=categories.id
	inner join brands
		on p.brand_id = brands.id
group by 3, 1, 2,4 order by count(*) desc
),
queryParamCategory as (
	select * from
		split_part((select category_code from categories c 
					inner join products p2 
						on p2.category_id = c.id 
						where p2.id = $1 limit 1), '.', 1)
)
select * 
from frequentlyBoughtTogether fbt
where split_part(fbt.category_code, '.', 1) = (select * from queryParamCategory) limit $2;

--1005073 electronics.smartphone

--- Find peak hours for a given day
with
allEventsFromDay as (
	select extract(hour from event_time)
	from events e 
	where date(e.event_time) = '2019-11-13'
)
select *, count(*) as no_of_events from allEventsFromDay
group by 1 order by count(*) desc limit 5;