SQL

select product_id from Products where low_fats = 'Y' and recyclable = 'Y'

select name from Customer where referee_id != 2 or referee_id is null

select name, population, area from world where area >= 3000000 or population >= 25000000

select distinct author_id as id from Views where author_id = viewer_id order by id

select tweet_id from Tweets where char_length(content) > 15

select EmployeeUNI.unique_id, Employees.name from Employees left join EmployeeUNI on Employees.id = EmployeeUNI.id

select p.product_name, s.year, s.price
from Sales s
join Products p on s.product_id = p.product_id

select v.customer_id, count(v.customer_id) as count_no_trans
from Visits v
left join Transactions  t on v.visit_id = t.visit_id
where t.visit_id is null
group by v.customer_id

select w1.id 
from Weather w1
join Weather w2
ON datediff(w1.recordDate, w2.recordDate) = 1
where w1.Temperature > w2.Temperature;

select machine_id,
	round(avg(end_time - start_time), 3) as processing_time
from (
	select
		machine_id,
		process_id,
		max(case when activity_type = 'start' then timestamp end) as start_time,
		max(case when activity_type = 'end' then timestamp end) as end_time
	from Activity
	group by machine_id, process_id
) as t
group by machine_id;

select 
	a.name, 
	b.bonus 
from Employee as a
left join Bonus as b
	on a.empID = b.empID
where b.bonus < 1000
	or b.bonus is null

with all_pairs as (
	select 
		s.student_id,
		s.student_name,
		sub.subject_name
	from Students as s
	cross join Subjects as sub
),
exam_count as (
	select
		student_id,
		subject_name,
		count(*) as cnt
	from Examinations
	group by student_id, subject_name
)
select 
	ap.student_id,
	ap.student_name,
	ap.subject_name,
	coalesce(ec.cnt, 0) as attened_exams
from all_pairs as ap
left join exam_count as ec
	on ap.student_id = ec.student_id
	and ap.subject_name = ec.subject_name
order by
	ap.student_id
	ap.subject_name

select
	m.name
from Employee as m
join Employee as e
	on e.managerId = m.id
group by
	m.id, m.name
having
	COUNT(e.id) >= 5;

select
	s.user_id,
	round(
		case
			when count(c.user_id) = 0 then 0
			else sum(case when c.action = 'confirmed' then 1 else 0 end) * 1.0 / count(c.user_id)
		end
	, 2) as confirmation_rate 
from Signups as s
left join Confirmations as c
	on s.user_id = c.user_id
group by
	s.user_id
	

select 
	*
from cinema 
where description <> 'boring' 
	and mod(id, 2) = 0
order by rating desc

select 
	p.product_id,
	round(
		case
			when sum(u.units) = 0 or sum(u.units) is null then 0
			else sum(p.price * u.units) / sum(u.units)
		end
	,2) as average_price
from Prices as p
left join UnitsSold as u
	on p.product_id = u.product_id and u.purchase_date between p.start_date and p.end_date
group by
p.product_id;

select
	p.project_id,
	round(
		avg(e.experience_years)
	,2) as average_years
from Project as p
left join Employee as e
	on p.employee_id = e.employee_id
group by 
	p.project_id

select 
	r.contest_id,
	round(count(*) * 100.0 / (select count(*) from Users), 2) as percentage
from Users as u
join Register as r
	on u.user_id = r.user_id
group by 
	r.contest_id
order by 
	percentage desc,
	r.contest_id asc

select 
	query_name,
	round(avg(rating*1.0/position),2) as quality,
	round(sum(case when rating < 3 then 1 else 0 end) * 100.0 / count(*), 2) as poor_query_percentage
from Queries
group by query_name;

select
	date_format(trans_date, '%Y-%m') as month,
	country,
	count(*) as trans_count,
	sum(case when state = 'approved' then 1 else 0 end) as approved_count,
	sum(amount) as trans_total_amount,
	sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from Transactions
group by date_format(trans_date, '%Y-%m'), country;

select
	round( sum(case when d.order_date = d.customer_pref_delivery_date then 1 else 0 end) *100/count(*),2) as immediate_percentage
from Delivery as d
join (
	select
		customer_id,
		min(order_date) as first_date
	from Delivery
	group by customer_id
) as f
	on d.customer_id = f.customer_id
	and d.order_date = f.first_date

with first_orders as (
	select
		customer_id,
	    order_date,
	    customer_pref_delivery_date,
	    row_number() over (
		    partition by customer_id
		    order by order_date
	    ) as rn
	from Delivery
)

select round (
	sum(case when order_date = customer_pref_delivery_date then 1 else 0 end) * 100 / count(*), 2) as immediate_percentage
from first_orders
where rn = 1;

SELECT
  ROUND(t_consec.num_consec * 1.0 / t_total.num_total, 2) AS fraction
FROM
  -- 子查询1：至少连续登录两天的去重玩家数
  (
    SELECT
      COUNT(DISTINCT a.player_id) AS num_consec
    FROM Activity AS a
    JOIN Activity AS b
      ON a.player_id = b.player_id
     AND DATEDIFF(b.event_date, a.event_date) = 1
  ) AS t_consec
  -- 子查询2：总玩家数（去重）
  CROSS JOIN
  (
    SELECT
      COUNT(DISTINCT player_id) AS num_total
    FROM Activity
  ) AS t_total;

select
	teacher_id,
	count(distinct subject_id) as cnt
from Teacher
group by teacher_id;

select
	customer_id
from Customer
group by
	customer_id
having
	count(distinct product_key) = (select count(*) from Product);

select
	'High Salary' as category,
	count(*) as accounts_count
from Accounts
where income > 50000

union all


select
	'Low Salary' as category,
	count(*) as accounts_count
from Accounts
where income < 20000

union all

select
	'Average Salary' as category,
	count(*) as account_count
from Accounts
Where income between 20000 and 50000

select
	d.name as Department,
	e.name as Employee,
	e.salary as Salary
from (
	select
		id,
		name,
		salary,
		departmentId,
		dense_rank() over(
			partition by departmentId
			order by salary desc
		) as rnk
	from Employee
) as e
join Department as d
	on e.departmentId = d.id
where
	e.rnk <= 3