SQL Hacks - Day 6

I had a good break in Argentina - road tripping on Route 40 (one of the most difficult roads in the world) and exploring Salta one day at a time!

Alright, so back on Leetcode!

Today, I learnt how to to calculate running sum in SQL.

So, you should use Partition by and over keywords to help you with the same.

For example, if you want to calculate the number of games played each individual player by a certain date.

Here is how you can do it:

select
player_id,
event_date,
sum(games_played) over (PARTITION BY player_id order by event_date) as games_played_so_far

from Activity

you need "partition by" because you need total games played by each player (segregating by id) and ordering by event date.

Another trick I learnt today about filtering records - like for example, having some value as 1 and other as 0. You can't simply use "where".

select
distinct (s.buyer_id)

from Sales s
inner join Product p ON p.product_id=s.product_id
group by s.buyer_id
having sum(p.product_name='S8')>0 and sum(p.product_name='iPhone')=0

Trick is using "sum" function here.

Another thing I learnt was that if you are applying any filters within the table, like for example - dates between x and y, some column following a condition - apply self join! Very efficient way. I used lead/lag to find if temperature on the next day was more than previous day which is an intuitive but applying self join on the conditions you want - was definitely a better one!

Another interesting concept I learnt was using count and partition together! I have always used group by and count together. But if you want to increase the level of detail, you should use partition :

COUNT(*) OVER (PARTITION BY department_id) 

I had to actually calculate percent rank based on rank and number of students in each department. There is actually a function called "percent rank" which I could have used.

SELECT student_id, department_id, 
    ROUND(100*PERCENT_RANK() OVER (
          PARTITION BY department_id 
          ORDER BY mark DESC)
    , 2) AS percentage 
FROM Students

Didn't know this!

Next functions I learnt about was "Least" greatest" - which can be used in sceanrios like where you want to calculate how many friends person 1 has (1->2 means than 2->1) so basically you convert all of the entries in proper ascending format. Like 1->2 and so next entry is also 1->2.

Alright, we are good for today!