Day: August 31, 2019

delete p1 
from Person p1, Person p2
where p1.Email = p2.Email 
and p1.Id > p2.Id

select 
id
from(
    select 
    id, 
    recorddate, 
    temperature,
    lag(temperature,1) over(order by recorddate) as previous_temperature,
    lag(recorddate,1) over(order by recorddate) as previous_date
    from weather
) w
where w.temperature > w.previous_temperature 
and datediff(w.recorddate, w.previous_date) = 1
order by 1

select 
request_at as day, 
round(sum(cancelled)/sum(total),2) as 'cancellation rate'
from( 
    -- client/user
    select 
    c.request_at, 
    count(distinct c.cancelled_id) as cancelled, 
    count(distinct c.id) as total
    from(
        select 
        t.request_at, 
        t.client_id, 
        t.status, 
        t.id,
        case 
            when t.status like 'cancelled%' then t.id 
            else null 
        end as cancelled_id
        from trips t
            join (
                select * 
                from users 
                where role = 'client' 
                and banned = 'No'
            ) u on t.client_id = u.users_id
        where request_at >='2013-10-01' 
        and request_at <='2013-10-03'
    ) c
    group by 1
    union all
    -- driver
    select 
    d.request_at, 
    count(distinct d.cancelled_id) as cancelled, 
    count(distinct d.id) as total
    from(
        select 
        t.request_at, 
        t.client_id, 
        t.status, 
        t.id,
        case 
            when t.status like 'cancelled%' then t.id 
            else null 
        end as cancelled_id
        from trips t
            join (
                select * 
                from users 
                where role = 'driver' 
                and banned = 'No'
            ) u on t.client_id = u.users_id
        where request_at >='2013-10-01' 
        and request_at <='2013-10-03'
    ) d
    group by 1
) a
group by 1
order by 1