A little bit about me

• Data Analyst @ Autodesk & Teaching Fellow @ Delta Analytics
• You can find me at RaulingAverage.dev
• Enjoy Coffee, Learning, and Running..near the beach

Notes:

• This presentation does not contain sensitive business information at Autodesk, but contains practices.

• I hope you and your families are well during these times! Moreover, I encourage y’all to Social Distance, Wear Masks, and keep an eye out to social movements during these next critical months.

What will we be talking about today?

SQL Readability

Scenario:

PR

-- Past 7 Days of Myspace User data.
select 

id, createdDate, events.login_id, geoState, firstName, lastName, sum(login.logIn) total_logins

from mySpace_events as events
LEFT OUTER JOIN mySpace_users users
on users.id = events.user_id
WHERE login.date >= current_date - (select min(createdDate) from mySpace_users where profile  = 'Musician')
group by 1,2,3,4,5,6

Congrats!

PR not approved...

Your query worked, so what happened?

It may turn out that you’re writing style may not be up-to-par with the team’s standards.

And that’s not your fault! It’s something you should not be ashamed about, but understanding about.

But...

Why should I care?

Situation

• Complex products or architectures contain a myriad of logic and code
• 1 year later, a new team member would like to change this query due to business logic modifications.
  • Consequently, teams or members have added another layer of complexity, which can actually put-off, confuse, or just reduce productivity for the new individual.

• Particularly, this new developer may experience:
  • Difficulty understanding logic and outputs
  • Longer debug time
  • Maintenance issues
  • The complexity of reading code

• With readability best practices, we alleviate said complexities. Thus, we can enable:
  • Increased Productivity
  • More time to focus on the core logic
  • Quicker time to implementation
• Synthesizing the engineering best practices with analytics

Convinced?

Let's get started!

UPPER CASE

Saved for Reserved Words & Functions

SELECT 

id, createdDate, events.login_id, geoState, firstName, lastName, SUM(login.logIn) total_logins

FROM mySpace_events AS events
LEFT OUTER JOIN mySpace_users users ON users.id = events.user_id
WHERE login.date >= CURRENT_DATE - (SELECT MIN(createdDate) FROM mySpace_users WHERE profile  = 'Musician')
GROUP BY 1,2,3,4,5,6

Spacing

“Do not optimize for a smaller number of lines of code. newlines are cheap, brain time is expensive” - Source

SELECT 

    id, 
    createdDate, 
    events.login_id, 
    geoState, 
    firstName, 
    lastName, 
    SUM(login.logIn) total_logins

FROM mySpace_events AS events

LEFT OUTER JOIN mySpace_users users ON
     users.id = events.user_id

WHERE login.date >= CURRENT_DATE - (SELECT MIN(createdDate) FROM mySpace_users WHERE profile  = 'Musician')

GROUP BY 1,2,3,4,5,6

80 Characters

SELECT 

    id, 
    createdDate, 
    events.login_id, 
    geoState, 
    firstName, 
    lastName, 
    SUM(login.logIn) total_logins

FROM mySpace_events AS events

LEFT OUTER JOIN mySpace_users users

ON users.id = events.user_id

WHERE login.date >= CURRENT_DATE - (
    SELECT 

        MIN(createdDate) 

    FROM mySpace_users 

    WHERE profile  = 'Musician'
    )

GROUP BY 1,2,3,4,5,6

Subqueries and WITH statements

WITH earliest_musician_user_date AS ( 

    SELECT 

        MIN(createdDate) 

    FROM mySpace_users 

    WHERE profile  = 'Musician'

)

SELECT 

    id, 
    createdDate, 
    events.login_id, 
    geoState, 
    firstName, 
    lastName, 
    SUM(login.logIn) total_logins

FROM mySpace_events AS events
LEFT OUTER JOIN mySpace_users users ON 
    users.id = events.user_id
WHERE login.date >= CURRENT_DATE - earliest_musician_user_date
GROUP BY 1,2,3,4,5,6

Aliases & Snake Case

WITH earliest_musician_user_date AS ( 

    SELECT 

        MIN(created_date)

    FROM mySpace_users 

    WHERE profile  = 'Musician'

)

SELECT 

    id, 
    created_date, 
    events.login_id, 
    geo_state, 
    first_name, 
    last_name, 
    SUM(login.logIn) AS total_logins

FROM mySpace_events AS events

LEFT OUTER JOIN AS myspace_users users

ON users.id = events.user_id

WHERE login.date >= CURRENT_DATE - earliest_musician_user_date

GROUP BY 1,2,3,4,5,6

Isn't that Better?

Recap

We learned by encouraging a readability practice for submitting code to a team’s repository, production, developers do not have to face:

• Difficult to understand logic and outputs
• Longer to debug
• Maintenance issues
• The complexity of reading code

And possible enable:

• Increased Productivity
• More time to focus on the core logic.
• Quicker turnover of fixes or tasks

Now,

Never RIGHT, move it to the LEFT..and SQL ON…

Thank you,

Resources

1. Vicki Boykis' take on readable SQL

2. SQL Style Guide

3. Craig Kersteins' take on Legible SQL

4. Quora: Why do we need to improve readability of our code?

5. Unofficial: My own past experience on SQL Readability form 2018 learnings

   Note: Company never open-sourced Readability standards for SQL, but has for others

6. Code Project; Writing SQL

7. PEP 8

8. Same post, on my blog