Month: October 2019

select 
user_id,
name,
mail
from Users
where 
mail regexp "^[a-zA-Z]+[a-zA-Z0-9_\\./\\-]{0,}@leetcode\\.com$"
order by user_id

select 
patient_id, 
patient_name, 
conditions
from Patients
where conditions like "%DIAB1%"
and conditions not like "SADIAB1%"

select 
o.name as customer_name, 
o.customer_id, 
o.order_id, 
o.order_date
from(
    select 
    o.customer_id, 
    c.name, 
    order_date, 
    order_id,
    dense_rank() over(partition by customer_id order by order_date desc) as r,
    count(order_id) over(partition by customer_id) as orders
    from Orders o
        left join Customers c 
            on o.customer_id= c.customer_id
) o
where (
o.r <= 3 
or o.orders < 3
    )
order by 1, 2, 4 desc

select 
lower(trim(product_name)) as product_name, 
date_format(sale_date,'%Y-%m') as sale_date, 
count(distinct sale_id) as total
from sales
group by 1,2
order by 1,2

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
    )

select distinct 
l.id, 
a.name
from(
    select 
    id, 
    grp, 
    min(login_date) as min_date, 
    max(login_date) as max_date
    from(
        select 
        id, 
        login_date, 
        r, 
        start_flag, 
        sum(start_flag) over(partition by id order by login_date) as grp
        from(
            select 
            id, 
            login_date, 
            r,
            case 
                when datediff(login_date, lag_login_date) > 1 or datediff(login_date, lag_login_date) is null then r 
                else 0 
            end as start_flag
            from(
                select 
                id, 
                login_date, 
                lag(login_date,1) over(partition by id order by login_date) as lag_login_date,
                row_number() over(partition by id order by login_date) as r
                from (
                    select distinct 
                    l.id, 
                    l.login_date
                    from(
                        select distinct 
                        id, 
                        login_date
                        from logins
                    ) l
                    left join (
                        select distinct 
                        id, 
                        login_date
                        from logins
                    ) l2 
                    on (l.id = l2.id and l.login_date <= date_add(l2.login_date, interval 1 day) )
                ) l
            ) l
        ) l
    ) l
    group by 1,2
    having datediff(max(login_date),min(login_date) ) >= 4
) l
left join accounts a 
    on l.id = a.id
order by l.id

select 
p1.id as p1, 
p2.id as p2, 
abs(p1.x_value - p2.x_value) * abs(p1.y_value - p2.y_value) as area
from Points p1
    cross join Points p2
where (
abs(p1.x_value - p2.x_value) * abs(p1.y_value - p2.y_value) > 0
and p1.id < p2.id
)
order by 3 desc, 1, 2

select 
s.company_id, 
s.employee_id, 
s.employee_name, 
round(s.salary - s.salary * s1.tax_rate, 0) as salary
from Salaries s
left join (
    select 
    s.company_id, 
    case 
        when s.max_salary < 1000 then 0 
        when s.max_salary >= 1000 and s.max_salary<=10000 then 0.24 
        when s.max_salary > 10000 then 0.49 
    end as tax_rate
    from (
        select 
        company_id, 
        max(salary) as max_salary 
        from Salaries 
        group by 1
    ) s
) s1 
    on s.company_id = s1.company_id

select 
item_category as category, 
sum(case when weekname = 'Monday' then quantity else 0 end) as Monday,
sum(case when weekname = 'Tuesday' then quantity else 0 end) as Tuesday,
sum(case when weekname = 'Wednesday' then quantity else 0 end) as Wednesday,
sum(case when weekname = 'Thursday' then quantity else 0 end) as Thursday,
sum(case when weekname = 'Friday' then quantity else 0 end) as Friday,
sum(case when weekname = 'Saturday' then quantity else 0 end) as Saturday,
sum(case when weekname = 'Sunday' then quantity else 0 end) as Sunday
from(
    select 
    i.item_category, 
    i.item_id, 
    o.order_date, 
    o.quantity,
    case 
        when weekday(o.order_date) = 0 then 'Monday'
        when weekday(o.order_date)=1 then 'Tuesday'
        when weekday(o.order_date) = 2 then 'Wednesday'
        when weekday(o.order_date) = 3 then 'Thursday'
        when weekday(o.order_date) = 4 then 'Friday'
        when weekday(o.order_date) = 5 then 'Saturday'
        when weekday(o.order_date) = 6 then 'Sunday'
    end as weekname
    from items i
        left join orders o 
            on i.item_id = o.item_id
) s
group by 1
order by 1

select 
s.student_id, 
s.student_name
from Student s
/* exclude student_ids with the highest or lowest scores */
where s.student_id not in (
    select distinct 
    a.student_id
    from (
        select 
        e1.exam_id, 
        e1.student_id, 
        e1.score, 
        e2.low_score, 
        e2.high_score
        from Exam e1
            left join(
                select 
                exam_id, 
                min(score) as low_score, 
                max(score) as high_score
                from Exam
                group by 1
            ) e2 
                on e1.exam_id = e2.exam_id
    where (e1.score = low_score 
           or e1.score = high_score )
    ) a
)
/* student_ids in all exams */
and s.student_id in (
    select distinct 
    student_id 
    from Exam 
    where exam_id is not null )
order by s.student_id

select 
q.id, 
q.year, 
ifnull(n.npv,0) as npv
from Queries q
    left join NPV n 
        on (q.id = n.id and q.year = n.year )

select 
b.bin, 
ifnull(s.total,0) as total
from (
    select '[0-5>' as bin
    union
    select '[5-10>' as bin
    union
    select '[10-15>' as bin
    union
    select '15 or more' as bin
) b
left join(
    select 
    bin, 
    count(distinct session_id) as total
    from(
        select 
        session_id, 
        duration,
        case 
            when duration/60 >= 0 and duration/60 < 5 then '[0-5>'
            when duration/60 >= 5 and duration/60 < 10 then '[5-10>'
            when duration/60 >= 10 and duration/60 < 15 then '[10-15>'
            when duration/60 >= 15 then '15 or more' 
            else null 
        end as bin
        from sessions
    ) s
    group by 1
) s
    on b.bin = s.bin

select 
a.left_operand, 
a.operator, 
a.right_operand, 
case 
    when a.operator = a.result then 'true' 
    else 'false' 
end as value
from(
    select 
    e.left_operand, 
    x.value as left_value, 
    e.operator, 
    e.right_operand, 
    y.value as right_value,
    case 
        when x.value = y.value then '='
        when x.value > y.value then '>'
        when x.value < y.value then '<'
    end as result
    from Expressions e
        left join Variables x 
            on e.left_operand = x.name
        left join Variables y 
            on e.right_operand = y.name
) a

select 
a.sale_date, 
(a.sold_num - o.sold_num) as diff
from (
    select 
    sale_date, 
    fruit, 
    sold_num
    from Sales
    where fruit = 'apples'
) a
left join (
    select 
    sale_date, 
    fruit, 
    sold_num
    from Sales
    where fruit = 'oranges'
) o 
    on a.sale_date = o.sale_date
order by 1