## 1517. Find Users with Valid E-Mails

Question:

Solution:

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

## 1527. Patients with a Condition

Question:

Solution:

``````select
patient_id,
patient_name,
conditions
from Patients
where conditions like "%DIAB1%"

## 1532. The Most Recent Three Orders

Question:

Solution:

``````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``````

## 1543. Fix Product Name Format

Question:

Solution:

``````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``````

## 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
)``````

## 1454. Active Users

Question:

Solution:

``````select distinct
l.id,
a.name
from(
select
id,
grp,
from(
select
id,
r,
start_flag,
sum(start_flag) over(partition by id order by login_date) as grp
from(
select
id,
r,
case
else 0
end as start_flag
from(
select
id,
row_number() over(partition by id order by login_date) as r
from (
select distinct
l.id,
from(
select distinct
id,
) l
left join (
select distinct
id,
) l2
) l
) l
) l
) l
group by 1,2
) l
left join accounts a
on l.id = a.id
order by l.id``````

Note: It will be helpful to review #1285 (Find the Start and End Number of Continuous Range) before working on this question.

## 1459. Rectangles Area

Question:

Solution:

``````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``````

## 1468. Calculate Salaries

Question:

Solution:

``````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``````

## 1479. Sales by Day of Week

Question:

Solution:

``````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``````

## 1412. Find the Quiet Students in All Exams

Question:

Solution:

``````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``````

## 1421. NPV Queries

Question:

Solution:

``````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 )``````

## 1435. Create a Session Bar Chart

Question:

Solution:

``````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``````

## 1440. Evaluate Boolean Expression

Question:

Solution:

``````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``````

## 1445. Apples and Oranges

Question:

Solution:

``````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``````

## 1384. Total Sales Amount by Year

Question:

Solution:

``````select
product_id,
product_name,
year as report_year,
(days_count * average_daily_sales) as total_amount
from(
select d.*, s.*,
case
/* start and end in the same year */
when year(s.period_end)-year(s.period_start) = 0 then datediff(period_end, period_start)+1
/* start and end across multiple years */
when year(s.period_end)-year(s.period_start) > 0 then
case
when year(s.period_start) = d.year then datediff(d.year_end, s.period_start) +1
when year(s.period_end) = d.year then datediff(s.period_end, d.year_start) +1
when year(s.period_start) < d.year and year(s.period_end) > d.year then datediff(d.year_end, d.year_start) +1
end
end as days_count
from(
select
date_format('2018-12-31','%Y') as year,
date_format('2018-01-01','%Y-%m-%d') as year_start,
date_format('2018-12-31','%Y-%m-%d') as year_end
union
select
date_format('2019-12-31','%Y') as year,
date_format('2019-01-01','%Y-%m-%d') as year_start,
date_format('2019-12-31','%Y-%m-%d') as year_end
union
select
date_format('2020-12-31','%Y') as year,
date_format('2020-01-01','%Y-%m-%d') as year_start,
date_format('2020-12-31','%Y-%m-%d') as year_end
) d
cross join (
select
s.*,
p.product_name
from Sales s
left join Product p
on s.product_id = p.product_id
) s
where (
d.year >= year(s.period_start)
and d.year <= year(s.period_end)
)
) a
order by product_id, year``````

## 1393. Capital Gain/Loss

Question:

Solution:

``````select
s.stock_name,
sum(s.sell_price - s.price) as capital_gain_loss
from(
select
*,
lead(operation,1) over(partition by stock_name order by stock_name, operation_day) as next_operation,
lead(price,1) over(partition by stock_name order by stock_name, operation_day) as sell_price
from Stocks
) s
where (
and s.next_operation = 'Sell'
)
group by 1``````

## 1398. Customers Who Bought Products A and B but Not C

Question:

Solution:

``````select
o.customer_id,
c.customer_name
from orders o
left join customers c
on o.customer_id = c.customer_id
where (
o.product_name in ('A','B')
and o.customer_id not in (
select distinct
customer_id
from orders
where product_name = 'C')
)
group by 1,2
having count(distinct product_name) >1
order by o.customer_id``````

## 1407. Top Travellers

Question:

``````select