## 1327. List the Products Ordered in a Period

Question:

Solution:

``````select
p.product_name,
o.unit
from Products p
join (
select
product_id,
sum(unit) as unit
from Orders
where year(order_date) = 2020
and month(order_date) = 2
group by 1
having sum(unit) >=100
) o
on p.product_id = o.product_id
group by 1,2``````

## 1336. Number of Transactions per Visit

Question:

Solution:

``````select
tt.transactions_count,
sum(tt.visits_count) as visits_count
from (
/* calculate visits based on existing transactions */
select b.transactions as transactions_count, count(user_id) as visits_count
from (
select
a.user_id,
visit_date,
transactions
from (
select
v.user_id,
v.visit_date,
s.transaction_date,
ifnull(s.transactions,0) as transactions
from Visits v
left join (
select
user_id,
transaction_date,
count(user_id) as transactions
from Transactions
group by 1,2
) s
on (v.user_id = s.user_id and v.visit_date = s.transaction_date )
) a
order by 3,2
) b
group by 1
union
/* create rows < max_transaction, including rows where there are transactions with 0 visits */
select
txn_row_num as transactions_count,
0 as visits_count
from (
select
row_number() over (order by transaction_date) -1 as txn_row_num
from Transactions
) t
join (
select
/* find the max_transaction with > 0 visits */
max(transactions) as max_txn
from(
select
v.user_id,
v.visit_date,
s.transaction_date,
ifnull(s.transactions,0) as transactions
from Visits v
left join (
select
user_id,
transaction_date,
count(user_id) as transactions
from Transactions
group by 1,2
) s
on (v.user_id = s.user_id and v.visit_date = s.transaction_date )
) a
) m
on t.txn_row_num <= m.max_txn
) tt
group by 1
order by 1``````

## 1341. Movie Rating

Question:

Solution:

``````select
results
from (
/* users who have rated the greatest number of movies */
select
u2.name as results
from (
select
u.user_id,
u.name,
u.movies,
rank() over(order by movies desc, name) as r
from (
select
r.user_id,
u.name,
count(distinct movie_id) as movies
from Movie_Rating r
on r.user_id = u.user_id
group by 1,2
) u
) u2
where u2.r = 1
union
/* movies with highest average rating in 2020-02 */
select m2.title as results
from(
select
m.movie_id,
m.title,
m.avg_rating,
rank() over(order by avg_rating desc, title) as r
from(
select
r.movie_id,
m.title,
avg(r.rating) as avg_rating
from Movie_Rating r
left join Movies m
on r.movie_id = m.movie_id
where substr(r.created_at,1,7) = '2020-02'
group by 1,2
) m
) m2
where m2.r = 1
) a``````

## 1350. Students with Invalid Departments

Question:

``````select