Data Science and Machine Learning Interview Handbook/

...

/

SQL Query Challenges

SQL Query Challenges

Explore writing SQL queries, including data insertion, various clauses, and real-world problem statements.

We'll cover the following...

SQL is one of the most commonly evaluated skills in data interviews. Whether you're analyzing customer behavior or optimizing internal dashboards, you’ll need to write clear, performant queries. In this lesson, we’ll practice using SQL within Python to answer complex real-world prompts. Let’s get started.

Writing a SQL Query

You’re given an Employees table with the following schema:

(id INTEGER, name TEXT, salary INTEGER, department_id INTEGER)

Your task is to write a SQL query that returns employees whose salary is greater than the average salary of their department.

Let’s assume the interviewer has defined a sample function taking care of basic table creation and data insertion for you. You need to fill out the find_high_earners function (line 38), implementing your SQL query with Python's pandas and sqlite3 library.

Press + to interact
import sqlite3

import pandas as pd



def initialize_employee_db():

    # Create an in-memory SQLite database

    conn = sqlite3.connect(':memory:')

    cursor = conn.cursor()

    # Create the Employees table

    cursor.execute('''

        CREATE TABLE Employees (

            id INTEGER PRIMARY KEY,

            name TEXT,

            salary REAL,

            department_id INTEGER

        )

    ''')

    # Insert dummy data

    employees_data = [

        (1, 'John Doe', 60000.00, 1),

        (2, 'Jane Smith', 75000.00, 1),

        (3, 'Bob Johnson', 55000.00, 1),

        (4, 'Alice Brown', 85000.00, 2),

        (5, 'Charlie Wilson', 95000.00, 2),

        (6, 'Diana Miller', 65000.00, 2),

        (7, 'Eva Davis', 120000.00, 3),

        (8, 'Frank Thomas', 110000.00, 3),

        (9, 'Grace Taylor', 90000.00, 3)

    ]

    #Define a query to populate the database

    cursor.executemany(

        'INSERT INTO Employees (id, name, salary, department_id) VALUES (?, ?, ?, ?)',

        employees_data

    )

    # Commit the changes

    conn.commit()

    return conn

    

def find_high_earners(conn):

    # TODO - Write your Query

    query = """

    """

    # TODO - execute the query and convert to DataFrame

    result_df = pd.read_sql_query(query, conn)

    #Return the dataframe as a result

    return result_df

Sample answer

This interview question tests understanding of several different SQL concepts, including: Common Table Expressions (WITH clause), JOINs, window functions and aggregation, and comparison operations. It also tests your knowledge of pandas and sqlite3.

Let’s dive into the solution code. The code defines a function find_high_earners that identifies employees who earn more than the average salary in their respective department. ...