The Dreaded SQL Screening

And How to F*cking Smash It!

Photo by Gabriel Tovar on Unsplash

In a past life, I studied electrical engineering.

It’s not that easy to wrap your head around EE material.

Those first couple of years, like most of my fellow students, I struggled to stay afloat.

I read and re-read the textbook, re-wrote notes, and spent countless hours … on the wrong approach!

What does this have to do with SQL screenings?

Everything :)

Eventually, after trial, error, research and conversing with smarter friends than I, I realized I could get better results with FAR less effort.

I could 80/20 that sh*t.

How?

In the same way you could f*cking crush the SQL screening:

1. Practice problems. Build familiarity with common SQL problems and data types.
2. A narrowed focus. Think from the perspective of your examiner. What’s most important? Focus just on that sh*t.

SQL is key as a data analyst!

Rather than developing a surface level understanding of everything, develop a deeper understanding of the essential.

Practice, practice, practice.

Photo by imgflip

Yeah, we’re talkin bout practice.

Most folks dread the SQL screening because they feel unprepared.

But flipping the script is easy as 1–2, 1–2 (check the bullets above).

There are patterns to this.

There are certain things the SQL examiner especially wants to make sure you know, certain things they’re going to assume you know, and certain things they could give two sh*ts if you know.

Without further adieu, let’s get into it.

What follows is a sample SQL screening (using PostgreSQL via pgAdmin).

It’s formatted as follows:

1. Tables. This allows you to familiarize yourself with the data.
2. Questions. These are the questions you’re to provide solutions to.
3. Question interpretations. Perceived difficulty level and particular SQL skill in question. To start thinking like the examiner.
4. Solutions. Corresponding solution SQL code and output.

With an understanding of the format, the choice is yours. You can:

1. Actively consume. Set a timer for 20:00, take the screening, and check your answers against the solutions.
2. Passively consume. Review all the content, including solutions, to see what you might expect, where you stand, and how you might approach it.

If you’re familiar with SQL, I’d highly recommend that you attempt the solutions yourself before consulting the provided solutions.

Active >>> passive consumption.

For “extra credit”, you can also use this screening to devise a couple datasets and screenings of your own.

Yes, it’s more work, but it puts YOU in the driver’s seat and into the mindset of Examiner.

This is great for learning and improving perspective.

‘Nuff talk. Let’s get into it!

THE SQL SCREENING

Background: you’re interviewing for a Data Analyst position with Company XYZ and they want to assess your SQL ability. They’ve prepared a 5 question SQL screening to be completed in 20 minutes time.

The further through the screening you get, the more likely you are to get a job and the higher your salary offer:

• 3 or fewer Qs correct → offer unlikely
• 4 Qs correct → likely offer with good salary
• 5 Qs correct → guaranteed offer with great salary

Directions: inspect the tables and write SQL queries to answer the questions provided below. There are 5 questions. You have 20 minutes.

Tables

Image by author

Questions

1. How many different events have been logged? And what’s the most popular `event`?
2. On which date were the most customers serviced?
3. What’s the name of the employee responsible for the most events? (How) would our results differ if we used a left vs. inner join?
4. What’s the average employee salary per position? And where does each employee rank with regard to salary for their position?
5. What job (employee_id + customer_id) took the longest to complete? And how long did it take?

Question Interpretation

1. Easy, warm up question assessing basic SQL and aggregation.
2. Easy-to-intermediate date/timestamp manipulation question.
3. Easy-to-intermediate aggregation and JOIN question.
4. Intermediate aggregate window function question.
5. Intermediate-to-advanced value window function question with CTE (common table expression).

I’ve taken and given numerous, numerous SQL screenings these past few years and can say with the utmost confidence that more-likely-than-not the question format you face will closely mirror what’s above.

Based on the company and role, certain types of questions may get more more emphasis (ie. timestamp manipulation) but you will see some form of

• SQL fundamentals (ie. COALESCE, CASE WHEN, DISTINCT),
• aggregation (ie. GROUP BY, AVG, SUM),
• JOINs (ie. INNER vs LEFT) vs UNION,
• manipulating timestamps (ie. EXTRACT, DATE_PART, DATE_TRUNC),
• subqueries or CTEs, and
• window functions.

If these topics are unfamiliar or you want a refresh, check out Nikolai Schuler’s Udemy course. It’s the single best online course I’ve ever taken.

Once well-versed in these areas, you’re ready to rock and roll!

If you want to attempt these questions on your own, now’s your chance. In the next section we go over the solutions.

Solutions

Q1 How many different events have been logged? And what’s the most popular `event`?

SELECT
  COUNT(*)
FROM event;

SELECT
  event,
  COUNT(*)
FROM event
GROUP BY 1
ORDER BY 2 DESC;

Answer: 12 events have been logged. All events (ie. Check in vs. Complete) have occurred at the same frequency. Thus, there is no “most popular” event.

Q2 On which date were the most customers serviced?

SELECT
  DATE_TRUNC('day', start_time) as start_date,
  COUNT(DISTINCT customer_id) as customers_serviced
FROM event
GROUP BY 1
ORDER BY 2 DESC;

Answer: I took “serviced” to mean that some work function, including issuing the PO, was completed. As such, the dates had an equivalent number of customers serviced. 3 customers were serviced on the 1st and 2nd of March 2023.

Q3 What’s the name of the employee responsible for the most events? (How) would our results differ if we used a left vs. inner join?

SELECT
  e1.name,
  COUNT(*) as events
FROM employees e1
INNER JOIN event e2
  ON e1.emp_id = e2.employee_id
GROUP BY 1
ORDER BY 2 DESC;

Answer: Willy Washington was responsible for the most events with 9.

In this particular instance, a LEFT JOIN would return all names from the employees table whereas an INNER JOIN would only return names with entries in the events table. While the outcome / top entry would be the same with either join, the subsequent entries would differ and could be misinterpreted. As such, we go with and recommend an INNER JOIN.

Q4 What’s the average employee salary per position? And where does each employee rank with regard to salary for their position?

SELECT *,
  ROUND(AVG(salary) OVER(PARTITION BY position),2) AS average_position_salary,
  RANK() OVER(PARTITION BY position ORDER BY salary DESC) AS position_salary_rank
FROM employees;

Query output

Q5 What job (employee_id + customer_id) took the longest to complete? And how long did it take?

WITH duration AS (
SELECT *,
  LEAD(start_time) OVER(PARTITION BY employee_id, customer_id ORDER BY start_time) as end_time,
  LEAD(start_time) OVER(PARTITION BY employee_id, customer_id ORDER BY start_time) - start_time as duration
FROM event
)

SELECT
  employee_id,
  customer_id,
  SUM(duration) as total_duration
FROM duration
GROUP BY 1,2
ORDER BY 3 DESC;

Query output

Answer: the job with employee_id 2 and customer_id 2 took the longest, with a total duration of 21 hours 34 minutes and 50 seconds.

There you have it!

The screening above is harder-than-average.

• If you did well on this, give yourself a pat on the back.
• If you didn’t do well, don’t sweat it. Keep working. Adopt that “win or learn” mindset and keep sharpening your skills. They take time.
• If you didn’t attempt it this time, build that competency and confidence then come back and give it a go.

Perfect practice makes perfect.

With enough practice, what was once a dreaded part of the interviewing process can become a highlight.

If you’re able to walk in confidently, the screening can be enjoyable.

It’s an opportunity to showcase your ability to query and communicate.

Confidence comes with competence.

As such, make sure you practice your ass off before your screening.

Thinking through the right approach, querying and responding to input, “whiteboarding” queries without output, explaining yourself and responding to input … they’re all learnable skills.

Whether studying electrical engineering or preparing for a SQL screening, better results can be had with FAR less effort if we focus on the right things.

A narrowed focus and practice, practice, practice.

Once familiar with the format and competent in the skill, you can smash this f*cking thing :)

You’ve got this.

Best of luck!