## 1484. Group Sold Products by Date

Question:

Solution:

``````select
sell_date,
count(distinct product) as num_sold,
group_concat(distinct product) as products
from activities
group by 1
order by 1``````

## 1495. Friendly Movies Streamed Last Month

Question:

Solution:

``````select distinct
c.title
from TVProgram p
left join content c
on p.content_id = c.content_id
where
c.Kids_content = 'Y'
and c.content_type = 'Movies'
and substring(p.program_date,1,7) = '2020-06'``````

## 1501. Countries You Can Safely Invest In

Question:

Solution:

``````select
b.country
from (
select
c.name as country,
sum(u.duration) as duration,
sum(u.calls) as calls,
sum(u.duration) / sum(u.calls) as avg_duration_country
from(
select
caller_id as id,
sum(duration) as duration,
count(caller_id) as calls
from Calls
group by 1
union
select
callee_id as id,
sum(duration) as duration,
count(callee_id) as calls
from Calls
group by 1
) u
left join (
select distinct
id,
substr(phone_number,1,3) as country_code
from Person
) p
on u.id = p.id
left join Country c
on p.country_code = c.country_code
group by 1
) b
where
b.avg_duration_country > (
select (2 * sum(duration)) / (2*count(caller_id)) as avg_duration_global
from Calls
)``````

## 1511. Customer Order Frequency

Question:

Solution:

``````select
c.customer_id,
c.name
from(
select
c.customer_id,
c.name,
ifnull(a.june_amount,0) as june_amount,
ifnull(b.july_amount,0) as july_amount
from Customers c
left join (
select
customer_id,
substring(o.order_date,1,7) as yrmo,
sum(o.quantity*p.price) as june_amount
from Orders o
left join Product p
on o.product_id = p.product_id
where
year(o.order_date) = 2020
and month(o.order_date) = 6
group by 1,2
having sum(o.quantity*p.price) >= 100
) a
on c.customer_id = a.customer_id
left join (
select
customer_id,
substring(o.order_date,1,7) as yrmo,
sum(o.quantity * p.price) as july_amount
from Orders o
left join Product p
on o.product_id = p.product_id
where
year(o.order_date) = 2020
and month(o.order_date) = 7
group by 1,2
having sum(o.quantity * p.price) >= 100
) b
on c.customer_id = b.customer_id
) c
where (
june_amount >=100
and july_amount>=100
)``````