## 196. Delete Duplicate Emails

Question:

Solution:

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

## 197. Rising Temperature

Question:

Solution:

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

## 262. Trips and Users

Question:

Solution:

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