[TIL]_2025.03.17 본캠프 29일차 (2): 코드카타 SQL

84번. [Customer Who Visited but Did Not Make Any Transactions]
(https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/description/)

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

85번. [Rising Temperature]
(https://leetcode.com/problems/rising-temperature/)

select w1.id
from weather w1
join weather w2
on (date_sub(w1.recorddate, interval 1 day)) = w2.recorddate
where w2.temperature < w1.temperature

86번. [Average Time of Process per Machine]
(https://leetcode.com/problems/average-time-of-process-per-machine/description/)

select 
    machine_id, 
    round(avg((timestamp - start_time)), 3) as processing_time
from (
    select *, 
        lag(timestamp) over(partition by machine_id order by process_id) as start_time
    from activity) as sub
where activity_type = 'end'
group by machine_id