Day: October 28, 2019

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

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'

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
)

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
    )