Skip to content

Instantly share code, notes, and snippets.

@Whoeza
Last active March 29, 2024 18:29
Show Gist options
  • Save Whoeza/42bc136fb969485d0d5a6753bf334e8e to your computer and use it in GitHub Desktop.
Save Whoeza/42bc136fb969485d0d5a6753bf334e8e to your computer and use it in GitHub Desktop.
SQL coding style: getting to the state of writing elegant SQL code
-- First, you want to break down the formatting style called "river",
-- where keywords align to form a river:
SELECT 'something', 'hey there',
'more stuff',
'42'
FROM dual
WHERE 1=1
;
-- After that's done, start dividing your database into sub-tables with the use of `with`,
-- and assemble queries by combining these `with`-subqueries.
-- Example: (medium-difficulty problem)
-- Find the fraction of players that logged in again on the day after
-- the day they first logged in, rounded to 2 decimal places.
-- This example was hard for me to get my head around it.
-- I've started dividing the problem into sub-problems, using the `with` clause, and solving the
-- sub-problems. And I can solve the main problem like a piece of cake!
-- Example:
-- 1- Find the total number of players. (it will be used as denominator to find the fraction)
--
-- 2- Find and display the rank of each login, for each player. (it will be used to find the
-- first 2 logins for each
-- player)
--
-- 3- Select the first 2 logins for each player. (it will be used to check which
-- players are returning on their
-- second login the day after
-- their first login)
--
-- 4- Define the returning players. (it will be used as numerator to find the fraction)
--
--
-- Now, we have all the sub-problems solved and can find the answer to our main question!
-- Thanks for reading.
-- If you liked this, consider sponsoring and/or hiring me! :)
-- . - . - . - . - . - . - . - . - . - . - . - . - . - . - . - . - --
/* The Code */
-----------------------------------------------------------------------------------------
-- First Step: define named subsets of queries, to be re-used, using the with clause! --
-----------------------------------------------------------------------------------------
WITH no_of_players AS
(
/* Find the total number of players. */
SELECT COUNT(DISTINCT player_id)
FROM Activity
),
player_logins AS
(
/* Display the rank for each login, for each player. */
SELECT player_id, event_date,
DENSE_RANK() OVER (PARTITION BY player_id ORDER BY event_date) AS login_no
FROM Activity
GROUP BY 1, 2
ORDER BY 1, 2
),
first_two_user_logins AS
(
/* From the subset of player logins, select the first 2 logins for each player. */
SELECT player_id, event_date
FROM player_logins
WHERE login_no < 3
),
returning_players AS
(
/* Define the returning players within their first 2 logins. */
SELECT DISTINCT player_id,
CASE WHEN LAG(event_date) OVER (PARTITION BY player_id) = ADDDATE(event_date, -1)
THEN 1
ELSE 0
END AS returning_player
FROM first_two_user_logins
)
------------------------------------------
-- Second step: query your subqueries! --
------------------------------------------
/* Find the fraction of players, over the entire playerbase,
who have logged in on their 1st and 2nd day.
*/
SELECT round( sum( returning_player ) / ( SELECT * FROM no_of_players ) , 2 ) AS fraction
FROM returning_players
@Whoeza
Copy link
Author

Whoeza commented Mar 27, 2024

I should mention that SQL key words go UPPERCASE, while names stay lowercase. :)

@Whoeza
Copy link
Author

Whoeza commented Mar 29, 2024

I should mention that SQL key words go UPPERCASE, while names stay lowercase. :)

Fixed!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment