8.24
1) Count Salary Categories
(select "High Salary" as category,
(select count(*)
from accounts
where income > 50000) as accounts_count)
union
(select "Average Salary" as category,
(select count(*)
from accounts
where income >= 20000 and income <= 50000) as accounts_count)
union
(select "Low Salary" as category,
(select count(*)
from accounts
where income < 20000) as accounts_count)
2) Confirmation Rate
select s.user_id,
case when c.action is null then 0
else round(sum(c.action='confirmed')/count(*),2) end as confirmation_rate
from Signups s
left join Confirmations c using(user_id)
group by 1
8.25
1) Odd and Even Transactions
select transaction_date,
sum(case when mod(amount,2)=1 then amount else 0 end) as odd_sum,
sum(case when mod(amount,2)=0 then amount else 0 end) as even_sum
from transactions
group by 1
order by 1
2) Find Students Who Improved
select *
from (
select s1.student_id,
s1.subject,
max(case when s1.exam_date = (select min(s2.exam_date) from Scores s2 where s2.student_id = s1.student_id and s2.subject = s1.subject) then s1.score end) as first_score,
max(case when s1.exam_date = (select max(s2.exam_date) from Scores s2 where s2.student_id = s1.student_id and s2.subject = s1.subject) then s1.score end) as latest_score
from Scores as s1
group by s1.student_id, s1.subject
) as t
where first_score < latest_score
order by student_id, subject
8.26
1) DNA Pattern Recognition
select sample_id,
dna_sequence,
species,
case when dna_sequence like 'ATG%' then 1 else 0 end as has_start,
case when regexp_like(dna_sequence, '(TAA|TAG|TGA)$') then 1 else 0 end as has_stop,
case when dna_sequence like '%ATAT%' then 1 else 0 end as has_atat,
case when dna_sequence like '%GGG%' then 1 else 0 end as has_ggg
from Samples
order by 1
2) Analyze Subscription Conversion
select user_id,
round(avg(case when activity_type = 'free_trial' then activity_duration end),2) as trial_avg_duration,
round(avg(case when activity_type = 'paid' then activity_duration end),2) as paid_avg_duration
from UserActivity
where user_id in (select user_id from UserActivity where activity_type = 'paid')
group by 1
order by 1
8.27
1) Find Product Recommendation Pairs
with pair as (
select p1.user_id,
p1.product_id as product1_id,
p2.product_id as product2_id
from ProductPurchases as p1
join ProductPurchases as p2 on p1.user_id = p2.user_id
and p1.product_id < p2.product_id
)
select p.product1_id,
p.product2_id,
p1.category as product1_category,
p2.category as product2_category,
count(distinct p.user_id) as customer_count
from pair as p
join ProductInfo as p1 on p.product1_id = p1.product_id
join ProductInfo as p2 on p.product2_id = p2.product_id
group by 1,2
having count(distinct p.user_id) >= 3
order by 5 desc, 1, 2
8.28
1) Seasonal Sales Analysis
with base as (
select case when month(s.sale_date) in (12,1,2) then 'Winter'
when month(s.sale_date) in (3,4,5) then 'Spring'
when month(s.sale_date) in (6,7,8) then 'Summer'
when month(s.sale_date) in (9,10,11) then 'Fall'
end as season,
p.category,
sum(s.quantity) as total_quantity,
sum(s.quantity*s.price) as total_revenue
from sales s
join products p using(product_id)
group by 1,2
)
select season, category, total_quantity, total_revenue
from (
select *,
row_number() over(partition by season order by total_quantity desc, total_revenue desc) as rn
from base
) as t
where rn = 1
order by season
