Category: SQL

select 
tweet_id
from Tweets
where length(content) > 15

select 
date_id, 
make_name,
count(distinct lead_id) as unique_leads,
count(distinct partner_id) as unique_partners
from DailySales
group by 1,2

select 
person1, 
person2, 
sum(calls) as call_count, 
sum(duration) as total_duration
from (
    select 
    from_id as person1, 
    to_id as person2, 
    1 as calls, 
    duration
    from Calls
    where from_id < to_id
    union all
    select 
    to_id as person1, 
    from_id as person2, 
    1 as calls, 
    duration
    from Calls
    where to_id < from_id
) c
group by 1,2

select 
a.machine_id, 
round(sum(a.duration)/count(distinct a.process_id),3) as processing_time
from(
    select 
    s.machine_id, 
    s.process_id, 
    (e.end_time- s.start_time) as duration
    from(
        select 
        machine_id, 
        process_id, 
        timestamp as start_time
        from Activity
        where activity_type = 'start'
        ) s
    left join (
        select 
        machine_id, 
        process_id, 
        timestamp as end_time
        from Activity
        where activity_type = 'end'
    ) e 
        on (s.machine_id = e.machine_id and s.process_id = e.process_id )
) a
group by 1
order by 1

select 
user_id, 
concat(upper(mid(name,1,1)),lower(mid(name,2))) as name
from Users
order by 1

select p.name, 
ifnull(sum(i.rest),0) as rest, 
ifnull(sum(i.paid),0) as paid,
ifnull(sum(i.canceled),0) as canceled,
ifnull(sum(i.refunded),0) as refunded
from Product p
    inner join Invoice i 
        on p.product_id = i.product_id
group by 1
order by 1

select 
d.month_num as month, 
ifnull(d.active_drivers,0) as active_drivers, 
ifnull(a.accepted_rides,0) as accepted_rides
from(
    select 
    m.month,  
    m.month_num, 
    count(distinct d.driver_id) as active_drivers
    from(
        select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
        union
        select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
        union
        select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
        union
        select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
        union
        select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
        union
        select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
        union
        select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
        union
        select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
        union
        select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
        union
        select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
        union
        select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
        union
        select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num 
    ) m
    left join (
        select 
        driver_id, 
        DATE_FORMAT(join_date,'%Y-%m') as join_month
        from Drivers
    ) d 
        on m.month >= d.join_month
    group by 1,2
) d
left join (
    select 
    DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
    count(distinct r.ride_id) as accepted_rides
    from Rides r
        join AcceptedRides a 
            on r.ride_id = a.ride_id
    group by 1
    having count(distinct r.ride_id) > 0
) a 
    on d.month = a.ride_month
order by 1

select 
d.month_num as month, 
case 
    when ifnull(d.active_drivers,0) = 0 then round(0,2)
    when ifnull(d.active_drivers,0) >0 then round((ifnull(a.accepted_rides,0)/ifnull(d.active_drivers,0))*100,2) 
end as working_percentage
from(
    select 
    m.month,  
    m.month_num, 
    count(distinct d.driver_id) as active_drivers
    from(
        select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
        union
        select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
        union
        select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
        union
        select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
        union
        select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
        union
        select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
        union
        select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
        union
        select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
        union
        select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
        union
        select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
        union
        select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
        union
        select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num 
    ) m
    left join (
        select 
        driver_id, 
        DATE_FORMAT(join_date,'%Y-%m') as join_month
        from Drivers
    ) d 
        on m.month >= d.join_month
    group by 1,2
) d
left join (
    select 
    DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
    count(distinct case when r.ride_id is not null then a.driver_id else null end) as accepted_rides
    from Rides r
        join AcceptedRides a 
            on r.ride_id = a.ride_id
    group by 1
    having count(distinct r.ride_id) >0
) a 
    on d.month = a.ride_month
order by 1

select 
month,
average_ride_distance,
average_ride_duration
from(  
    select 
    month,
    round(avg(ride_distance) over(order by month asc rows between current row and 2 following),2) as average_ride_distance,
    round(avg(ride_duration) over(order by month asc rows between current row and 2 following),2) as average_ride_duration
    from(
        select 
        b.month_num as month, 
        sum(ride_distance) as ride_distance,
        sum(ride_duration) as ride_duration
        from(
            select 
            m.month,  
            m.month_num, 
            d.driver_id, 
            ifnull(r.ride_distance,0) as ride_distance, 
            ifnull(r.ride_duration,0) as ride_duration 
            from(
                select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
                union
                select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
                union
                select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
                union
                select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
                union
                select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
                union
                select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
                union
                select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
                union
                select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
                union
                select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
                union
                select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
                union
                select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
                union
                select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num 
            ) m
            left join (
                select 
                driver_id, 
                DATE_FORMAT(join_date,'%Y-%m') as join_month
                from Drivers
            ) d 
                on m.month >= d.join_month
            left join (
                select 
                a.driver_id, 
                DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
                sum(a.ride_distance) as ride_distance,
                sum(a.ride_duration) as ride_duration
                from AcceptedRides a
                    left join Rides r 
                        on a.ride_id = r.ride_id
                group by 1,2
            ) r 
                on (m.month = r.ride_month and d.driver_id = r.driver_id )
        ) b
        group by 1
    ) c
) d
where month <= 10
order by 1

select a.id as ids
from (
    select 1 as id union
    select 2 as id union
    select 3 as id union
    select 4 as id union
    select 5 as id union
    select 6 as id union
    select 7 as id union
    select 8 as id union
    select 9 as id union
    select 10 as id union
    select 11 as id union
    select 12 as id union
    select 13 as id union
    select 14 as id union
    select 15 as id union
    select 16 as id union
    select 17 as id union
    select 18 as id union
    select 19 as id union
    select 20 as id union
    select 21 as id union
    select 22 as id union
    select 23 as id union
    select 24 as id union
    select 25 as id union
    select 26 as id union
    select 27 as id union
    select 28 as id union
    select 29 as id union
    select 30 as id union
    select 31 as id union
    select 32 as id union
    select 33 as id union
    select 34 as id union
    select 35 as id union
    select 36 as id union
    select 37 as id union
    select 38 as id union
    select 39 as id union
    select 40 as id union
    select 41 as id union
    select 42 as id union
    select 43 as id union
    select 44 as id union
    select 45 as id union
    select 46 as id union
    select 47 as id union
    select 48 as id union
    select 49 as id union
    select 50 as id union
    select 51 as id union
    select 52 as id union
    select 53 as id union
    select 54 as id union
    select 55 as id union
    select 56 as id union
    select 57 as id union
    select 58 as id union
    select 59 as id union
    select 60 as id union
    select 61 as id union
    select 62 as id union
    select 63 as id union
    select 64 as id union
    select 65 as id union
    select 66 as id union
    select 67 as id union
    select 68 as id union
    select 69 as id union
    select 70 as id union
    select 71 as id union
    select 72 as id union
    select 73 as id union
    select 74 as id union
    select 75 as id union
    select 76 as id union
    select 77 as id union
    select 78 as id union
    select 79 as id union
    select 80 as id union
    select 81 as id union
    select 82 as id union
    select 83 as id union
    select 84 as id union
    select 85 as id union
    select 86 as id union
    select 87 as id union
    select 88 as id union
    select 89 as id union
    select 90 as id union
    select 91 as id union
    select 92 as id union
    select 93 as id union
    select 94 as id union
    select 95 as id union
    select 96 as id union
    select 97 as id union
    select 98 as id union
    select 99 as id union
    select 100 as id 
) a
left join Customers c 
    on a.id = c.customer_id
where (
    c.customer_id is null
    and a.id <= (select max(customer_id) from Customers)
    )
order by 1

select 
a.student_name as member_A,
b.student_name as member_B,
c.student_name as member_C
from SchoolA a
    cross join SchoolB b
    cross join SchoolC c
where
a.student_name <> b.student_name
and b.student_name <> c.student_name
and a.student_name <> c.student_name
and a.student_id <> b.student_id
and b.student_id <> c.student_id
and a.student_id <> c.student_id

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

select 
v.customer_id, 
count(distinct v.visit_id) as count_no_trans
from visits v
    left join transactions s 
        on v.visit_id = s.visit_id
where s.amount is null
group by 1

select 
name, 
balance
from(
    select 
    t.account, 
    u.name, 
    sum(amount) as balance
    from transactions t
        left join users u 
            on t.account = u.account
    group by 1,2
    having sum(amount) > 10000
) a

select 
b.customer_id,
b.product_id,
p.product_name
from(
    select 
    customer_id,
    product_id,
    order_count,
    rank() over(partition by customer_id order by order_count desc) as r
    from(
        select 
        o.customer_id,
        o.product_id,
        count(distinct o.order_id) as order_count
        from Orders o
        group by 1,2
        having count(distinct o.order_id) >= 1 /* at least 1 order */
    ) a
) b
left join (
    select 
    product_id,
    product_name
    from Products
    group by 1,2
) p 
    on b.product_id = p.product_id
where b.r = 1

select
seller_name
from Seller s
/* sellers who haven't made any sales in 2020 */
where seller_id in (
    select 
    seller_id
    from Orders
    group by 1
    having max(year(sale_date)) <> 2020
    and min(year(sale_date)) <> 2020
)
/* sellers who haven't sold nothing */
or seller_id not in (
    select distinct
    seller_id
    from Orders
)
order by 1