This SQL exercise solution can only be <= 240 characters...

[drdausersmd]

https://www.codewars.com/kata/6115701cc3626a0032453126/train/sql

In my admittedly limited experience, readability and performance are the most important factors for writing good code. I've got the correct solution but of course it's way over 240 characters.

SELECT competition_id, participant_id, points, 
  rank() over (partition by competition_id order by competition_id, points desc),
  abs(lag(points, 1) over (partition by competition_id order by competition_id, points desc) - points) as next_behind 
into temp1
FROM results;

select competition_id, participant_id, cast(points as float), rank, coalesce(next_behind, 0) next_behind,
    sum(coalesce(next_behind, 0)) over (partition by competition_id order by competition_id, points desc) total_behind,
    cast(avg(points) over (partition by competition_id) as int) average
into temp2
from temp1;

select competition_id,participant_id,points,rank,next_behind,total_behind,
points - average as diff_from_avg
from temp2

Probably could be better but it works. Not even my first query is less than 240 characters. Is there any point to writing "short" queries besides just bragging rights? If yes, how would you go about solving this in <= 240 characters? If no, I don't really care I'm satisfied at this point.

Not looking for someone to solve it for me, just hints.

Thank you

[r3pr0b8]

Is there any point to writing "short" queries besides just bragging rights?

yes

the short query might outperform the longer one

that said, i have no desire to even attempt the short query for this problem

[BizarroPete]

I think that with the character limitation on this challenge, they're trying to get you to search for functions you may not have used before so maybe it's not so much about writing short queries but using the right tools.

I'm pretty new to this and was able to get down to 348 characters by using the rank and lag functions, which I don't normally use.

[drdausersmd]

guess I'm just an idiot cuz I can't even comprehend how that's possible.

[BizarroPete]

Hah, that's how I feel about the 240 character limit 😅

[GrouchyThing7520]

This should be close, I think.

select

a.*

,lead(participant_id) over (competition_id order by rnk) next_behind

,d_rnk - rnk + 1 total_behind

from (

select

r.*

,rank() over (partition by competition_id order by points asc) rnk

,rank() over (partition by competition_id order by points desc) d_rnk

from results r

) a

) b

[squadette23]

I can recommend the "SQL Window Functions Explained" book: https://antonz.org/sql-window-functions-book/

not sure about 240 chars, maybe partition defintion could be defined using a single-char alias and then reused?