Day: October 30, 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