SQL Hacks - Day 5

Off to a very productive morning today!

Brushing my SQL skills again today.

I had to count distinct based on multiple columns in different tables in a problem.

I used "concat" function to join the columns which I required and then did a count distinct on that.

Alternatively I could have used count(*) from a table which already had distinct rows based on those columns.

Another interesting problem I learnt to solve was how to calculate sum of columns (lets say x,y) just do x+y and also select x,y so that it is iterated over every row

Sum will give you a single value - so don't use that.

Also, did some problems on Lead and Lag - super important concepts!

this finds shortest distance between any two numbers in "x" column

select
min(dist) as shortest
from
(select
abs(x-lead(x) over (order by x)) as dist
from Point
order by x ASC) a

<Syntax of lead/lag>
Select c_id, start_date, end_date, 
        end_date - lead (start_date) 
        over (order by start_date) 
               + 1 as 'no_of_days' 
                   from contest;
                   
       

Source: Geekforgeeks

Finding consecutive seats in a cinema hall:

select
*
from(

select
case
when (lag(free) over (order by seat_id) = 1 and free = 1) then seat_id
when (lead(free) over (order by seat_id) = 1 and free = 1) then seat_id
else NULL
end as seat_id

from cinema) a
where seat_id IS NOT NULL;

Another problem using "case" and lead, lag
select
distinct ConsecutiveNums
from
(Select
CASE 
when (lead(num) over (order by id) = num and lead(num,2) over (order by id) = num) then num
else NULL
end as ConsecutiveNums
from Logs) a
where ConsecutiveNums is not null

Alright, that's all folks for today! I solved 8 problems today, super happy :D

It's freezing cold in Indiana, stay safe!