SQL coding style: getting to the state of writing elegant SQL code

sql-elegant-coding-style.sql

-- 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

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

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

Fixed!