Day: September 20, 2019

select 
s.id, 
case 
    when type = 'odd' and next_student is not null then next_student
    when type = 'odd' and next_student is null then student
    when type = 'even' then prev_student
    else null
end as student
from(
    select 
    id, 
    student,
    lead(student,1) over(order by id) as next_student,
    lag(student,1) over (order by id) as prev_student,
    case when id % 2 = 0 then 'even' else 'odd' end as type
    from seat
) s
order by id

update salary
set sex = (
    case 
        when sex = 'm' then 'f' 
        when sex = 'f' then 'm' 
    end
)

select 
c.customer_id
from(
    select 
    customer_id, 
    count(distinct product_key) as products
    from Customer 
    group by 1
) c
where c.products in (
    select 
    count(distinct product_key) as products 
    from Product
)

select distinct 
actor_id, 
director_id
from(
    select 
    actor_id, 
    director_id, 
    count(timestamp) as freq
    from ActorDirector
    group by 1,2
    having count(timestamp) >= 3
) a
order by 1,2