case study sql questions

SQL Case Study Interview Questions and Answers (2024)

What is a sql case study.

The majority of SQL interview questions are straightforward. You may be asked for definitions, or to write a clearly defined SQL query.

But SQL case study questions are an entirely different beast.

These questions usually start with a hypothetical business or product issue, e.g. unsubscribe rates are falling. Then, you have to define what metrics could be used to investigate the problem , and then write the query to produce those metrics.

One of the best ways to prepare for SQL case study interviews is to walk through solutions step-by-step. This will show you how to think about metrics in hypotheticals, as well as how to walk interviewers through your logic.

We’ve done that here, with two breakdowns of SQL case questions with clear solutions.

Example SQL Case Question: Unsubscribe Rates

While case study and SQL case study interview questions can cover a variety of topics, some may specifically require finding correlations as part of the analysis. In this example SQL case question , we’re looking into this issue: Unsubscribe rates have increased after a new notification system has been introduced.

Twitter wants to roll out more push notifications to users because they think users are missing out on good content. Twitter decides to do this in an A/B test.

Say that after more notifications are released, there is a sudden increase in the total number of unsubscribes.

We’re given two tables: events where actions are ‘login’, ‘nologin’, and ‘unsubscribe’ and another table called variants where user’s are bucketed into a control and a variant A/B test.

Given these tables, write a query to display a graph to understand how unsubscribes are affecting login rates over time.

Note: Let’s say that all users are automatically put into the A/B test.

events table

variants table

Step 1: Start Each SQL Case Study by Making Assumptions

This question asks us to compare multiple variables at play here. Specifically, we’re looking at:

• There is a new notification system.
• We’re interested in the effect the new notifications are having on unsubscribes.

We’re not sure how unsubscribes are affecting login rates, but we can plot a graph that would help us visualize how the login rates change before and after an unsubscribe from a user .

We can also see how the login rates compare for unsubscribes for each bucket of the A/B test. Given that we want to measure two different changes, we have to eventually do a GROUP BY of two different variables:

• Bucket variant

Step 2: Develop a Hypothesis for the SQL Case Question

In order to visualize this, we’ll need to plot two lines on a 2D graph.

• The x-axis represents days until unsubscribing with a range of -30 to 0 to 30, in which -30 is thirty days before unsubscribing and 30 is 30 days after unsubscribing.
• The y-axis represents the average login rate for each day. We’ll be plotting two lines for each of the A/B test variants, control and test.

Now that we have what we’re going to graph, it’s a matter of writing a SQL query to get the dataset for the graph.

We can make sure our dataset looks something like this:

Each column represents a different axis or line for our graph.

Step 3: SQL Coding + Analysis

We know that we have to get every user that has unsubscribed, so we’ll first INNER JOIN the abtest table to the events table, where there exists an unsubscribe event. Now we’ve isolated all users that have ever unsubscribed.

Additionally, we have to then get every event in which the user has logged in, and divide it by the total number of users that are eligible within the timeframe.

Example SQL Case Question: LinkedIn Job Titles

Many SQL case questions require creativity to solve. You’re given a hypothesis, but then have to determine how to prove or disprove it with specific metrics. The key here is walking the interviewer through your thought process. This example SQL case question from LinkedIn explores user career paths.

We’re given a table of user experiences representing each person’s past work experiences and timelines.

Specifically, let’s say we’re interested in analyzing the career paths of data scientists. The titles we care about are bucketed into data scientist, senior data scientist, and data science manager.

We’re interested in determining if a data scientist who switches jobs more often ends up getting promoted to a manager role faster than a data scientist that stays at one job for longer.

Write a query to prove or disprove this hypothesis.

user_experiences table

Step 1: Make Assumptions about the SQL Case Question

The hypothesis is that data scientists that end up switching jobs more often get promoted faster.

Therefore, in analyzing this dataset, we can prove this hypothesis by separating the data scientists into specific segments based on how often they shift in their careers.

For example, if we look at the number of job switches for data scientists that have been in their field for five years, we could prove the hypothesis if the number of data science managers increased along with the number of career jumps.

Here’s what that might look like:

• Never switched jobs: 10% are managers
• Switched jobs once: 20% are managers
• Switched jobs twice: 30% are managers
• Switched jobs three times: 40% are managers

We could look at this over different buckets of time as well to see if the correlation stays consistent after 10 or 15 years in a data science career.

This analysis proves to be correct except for the fact that it doesn’t count the intention of the data scientist. What happens if the data scientist didn’t ever want to become a manager?

Step 2: Come up with a Hypothesis for the SQL Case Question

There’s one flaw in the assumption there. It doesn’t account for the intention of the data scientist. It doesn’t answer the question: What happens if the data scientist didn’t ever want to become a manager?

One way to solve this is to do the analysis backwards .

We can subset all of the existing data science managers and see how often they ended up switching jobs before they got to their first manager position.

Then divide the number of job switches by the amount of time it took for them to achieve the manager position themselves. This way, we can end up with a result that looks like this:

• Job switches: 1 - Average months to promotion: 50
• Job switches: 2 - Average months to promotion: 46
• Job switches: 3 - Average months to promotion: 44

But there is a fault with this analysis as well. What about all those data scientists that have switched jobs / not switched jobs but haven’t become managers yet? They could be one month away from being a manager and be subsetted out of our analysis!

We have to then make some assumptions about the distribution of existing data science managers.

Are the years of experience before they became managers normally distributed? If not, then our results might be a bit biased from our hindsight analysis.

Step 3: Write the SQL Case Query

We first make a CTE called manager_promo with all the user_ids that have been promoted to data science managers.

Next, we count the number of job switches before getting promoted as num_jobs_switched.

Then, we calculate the number of months before promotion to the data science manager position as month_to_promo.

Finally, we order by the number of jobs switched.

Step 4: Perform Analysis and Make Conclusions

Hint: Talk about any conclusions you could draw from your data, but also be prepared to talk about trade-offs and potential flaws.

With the query result, we can draw conclusions about the months it took each distinct user to be promoted to data science manager.

Be warned this solution is not perfect. The edge cases where users never become promoted to data science managers are not considered.

Finally, many adjustments, like creating buckets for different ranges of months (0-20 months to promotion, 20-40 months to promotion, etc.), can present a more digestible, high-level analysis on whether frequent job changes affect promotion opportunities to the data science manager position.

Each bucket would correspond to the average time it took the users in that bucket to be promoted to a data science manager position.

Learn more about SQL questions

This course is designed to help you learn everything you need to know about working with data, from basic concepts to more advanced techniques.

More SQL Resources to Ace Your Interview

If you have an interview coming up, review Interview Query’s data science course, which includes modules in SQL .

SQL interviews are demanding, and the more you practice all types of SQL interview questions and not just case questions, the more confident and efficient you’ll become in answering them.

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications You must be signed in to change notification settings

A comprehensive collection of SQL case studies, queries, and solutions for real-world scenarios. This repository provides a hands-on approach to mastering SQL skills through a series of case studies, including table structures, sample data, and SQL queries.

tituHere/SQL-Case-Study

Folders and files.

DEV Community

Posted on Oct 11, 2022

8 Week SQL Challenge: Case Study #1 Danny’s Diner

Introduction

Danny seriously loves Japanese food so in the beginning of 2021, he decides to embark upon a risky venture and opens up a cute little restaurant that sells his 3 favourite foods: sushi, curry and ramen.

Danny’s Diner is in need of your assistance to help the restaurant stay afloat — the restaurant has captured some very basic data from their few months of operation but have no idea how to use their data to help them run the business.

Problem Statement

Danny wants to use the data to answer a few simple questions about his customers, especially about their

• visiting patterns,
• how much money they’ve spent, and
• which menu items are their favourite. Having this deeper connection with his customers will help him deliver a better and more personalised experience for his loyal customers.

He plans on using these insights to help him decide whether he should expand the existing customer loyalty program — additionally he needs help to generate some basic datasets so his team can easily inspect the data without needing to use SQL.

The data set contains the following 3 tables which you may refer to the relationship diagram below to understand the connection

Table Relationship

Case Study Questions

1.What is the total amount each customer spent at the restaurant? 2.How many days has each customer visited the restaurant? 3.What was the first item from the menu purchased by each customer? 4.What is the most purchased item on the menu and how many times was it purchased by all customers? 5.Which item was the most popular for each customer? 6.Which item was purchased first by the customer after they became a member? 7.Which item was purchased just before the customer became a member? 8.What is the total items and amount spent for each member before they became a member? 9.If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have? 10In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi — how many points do customer A and B have at the end of January?

I’m using Microsoft SQL Server and these are the functions used.

• Aggregate functions — SUM, MIN, MAX
• Numerical functions — TOP
• Joins — Inner join, left join
• Temp tables (CTE)
• Windows function
• What is the total amount each customer spent at the restaurant? We use the SUM and GROUP BY functions to find out total spent for each customer and JOIN function because customer_id is from sales table and price is from menu table.

• Customer A spent $76.
• Customer B spent $74.
• Customer C spent $36.
• How many days has each customer visited the restaurant?

Use DISTINCT and wrap with COUNT function to find out number of days customer visited the restaurant.

If we do not use DISTINCT for order_date, the number of days may be repeated. For example, if customer A visited the restaurant twice on ‘2021–01–07’, then number of days may have counted as 2 instead of 1 day.

• Customer A visited 4 times.
• Customer B visited 6 times.
• Customer C visited 2 times.
• What was the first item from the menu purchased by each customer?

First, we have to create a CTE using WITH function. In the summary CTE, we use DENSE_RANK and OVER(PARTITION BY ORDER BY) to create a new column rank based on order_date.

I chose to use DENSE_RANK instead of ROW_NUMBER or RANK as the order_date is not time stamped hence, we do not know which item is ordered first if 2 or more items are ordered on the same day.

Subsequently, we GROUP BY the columns to show rank = 1 only.

• Customer A’s first order are curry and sushi.
• Customer B’s first order is curry.
• Customer C’s first order is ramen.
• What is the most purchased item on the menu and how many times was it purchased by all customers?

• Most purchased item on the menu is ramen. Yummy!
• Which item was the most popular for each customer?

Again, we create a CTE to rank the number of orders for each product by DESC order for each customer.

Then, we generate results where rank of product = 1 only as the most popular product for individual customer.

• Customer A and C’s favourite item is ramen.
• Customer B enjoys all items in the menu. He/she is a true foodie.
• Which item was purchased first by the customer after they became a member?

Yeap, you can guess it! We’re creating another CTE.

In this CTE, we filter order_date to be on or after their join_date and then rank the product_id by the order_date.

Next, we filter the table by rank = 1 to show first item purchased by customer.

After Customer A became a member, his/her first order is curry, whereas it’s sushi for Customer B.

• Which item was purchased just before the customer became a member?

Basically this is a reversed of Question #6. Create a CTE in order

Create new column rank by partitioning customer_id by DESC order_date to find out the order_date just before the customer became member

Filter order_date before join_date.

Then, pull table to show the last item ordered by customer before becoming member.

• Customer A’s order before he/she became member is sushi and curry and Customer B’s order is sushi. That must have been a real good sushi!
• What is the total items and amount spent for each member before they became a member?

First, filter order_date before their join_date. Then, COUNT unique product_id and SUM the prices total spent before becoming member.

Answer: Before becoming members,

• Customer A spent $ 25 on 2 items.
• Customer B spent $40 on 2 items.
• If each $1 spent equates to 10 points and sushi has a 2x points multiplier — how many points would each customer have?

Let’s breakdown the question.

• Each $1 spent = 10 points.

But, sushi (product_id 1) gets 2x points, meaning each $1 spent = 20 points So, we use CASE WHEN to create conditional statements

If product_id = 1, then every $1 price multiply by 20 points

All other product_id that is not 1, multiply $1 by 10 points

So, you can see the table below with new column, points.

Using the table above, we SUM the price, match it to the product_id and SUM the total_points.

• Total points for Customer A, B and C are 860, 940 and 360.
• In the first week after a customer joins the program (including their join date) they earn 2x points on all items, not just sushi — how many points do customer A and B have at the end of January?

Again, we breakdown the question.

• Find out customer’s validity date (which is 6 days after join_date and inclusive of join_date) and last day of Jan 2021 (‘2021–01–21’).

Then, use CASE WHEN to allocate points by dates and product_name.

Our assumptions are

• Day -X to Day 1 (customer becomes member (join_date), each $1 spent is 10 points and for sushi, each $1 spent is 20 points.
• Day 1 (join_date) to Day 7 (valid_date), each $1 spent for all items is 20 points.

Day 8 to last day of Jan 2021 (last_date), each $1 spent is 10 points and sushi is 2x points. Answer:

Customer A has 1,370points.

Customer B has 820 points.

Bonus Questions

Join All The Things Recreate the table with: customer_id, order_date, product_name, price, member (Y/N)

Rank All The Things

Danny also requires further information about the ranking of customer products, but he purposely does not need the ranking for non-member purchases so he expects null ranking values for the records when customers are not yet part of the loyalty program.

From the analysis, we discover a few interesting insights that would be certainly useful for Danny.

• Customer B is the most frequent visitor with 6 visits in Jan 2021.
• Danny’s Diner’s most popular item is ramen, followed by curry and sushi.
• Customer A and C loves ramen whereas Customer B seems to enjoy sushi, curry and ramen equally. Who knows, I might be Customer B!
• Customer A is the 1st member of Danny’s Diner and his first order is curry. Gotta fulfill his curry cravings!
• The last item ordered by Customers A and B before they became members are sushi and curry. Does it mean both of these items are the deciding factor? It must be really delicious for them to sign up as members!
• Before they became members, both Customers A and B spent $25 and $40.
• Throughout Jan 2021, their points for Customer A: 860, Customer B: 940 and Customer C: 360.
• Assuming that members can earn 2x a week from the day they became a member with bonus 2x points for sushi, Customer A has 660 points and Customer B has 340 by the end of Jan 2021.

Thank you Danny Ma for the excellent case study! You can find it here and try it yourself.

Top comments (0)

Templates let you quickly answer FAQs or store snippets for re-use.

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink .

Hide child comments as well

For further actions, you may consider blocking this person and/or reporting abuse

Getting Started with AI for Developers: Part 1 - Demystifying the Basics

TinoMuchenje - Sep 9

How to Deploy Your React Project with a Custom Domain Using AWS?

Shanu - Aug 21

Debugging Your Finances: Open Source Future Wealth Calculator for your investments

Lakshit Somani - Sep 8

SQL Query Logical Order

Pranav Bakare - Sep 8

We're a place where coders share, stay up-to-date and grow their careers.

Top 25 SQL Case Expression Interview Questions and Answers

Prepare for your next interview with our comprehensive guide on SQL Case Expression. This article offers a detailed rundown of potential interview questions and answers to help you succeed.

SQL, or Structured Query Language, is a standardized programming language that’s used for managing and manipulating relational databases. Among its many features, the SQL Case Expression holds a special place due to its versatility and practicality in various scenarios. It provides developers with the ability to perform conditional logic in SQL statements, thus adding a layer of dynamism to data retrieval and manipulation.

In the realm of SQL, the CASE expression is akin to IF-THEN-ELSE statements found in other programming languages. It allows us to create different outputs in our SELECT clause based on conditions in our data. This powerful tool can greatly enhance your SQL queries, making them more efficient and adaptable.

In this article, we’ve compiled an extensive list of interview questions focused on the SQL Case Expression. These questions will not only test your understanding of this particular feature but also illustrate its application in real-world situations, thereby enhancing your overall proficiency in SQL.

1. Can you explain what a SQL Case Expression is and where it is typically used?

A SQL Case Expression is a conditional statement that provides a way to perform IF-THEN-ELSE logic within an SQL query. It allows for complex, multi-condition queries and can be used in any clause or statement that accepts a valid expression. There are two types: Simple CASE (compares an expression to set values) and Searched CASE (evaluates multiple Boolean expressions).

Typically, it’s used in SELECT, UPDATE, DELETE, and WHERE clauses to create more flexible data manipulation statements. For instance, you might use it to categorize data into different groups based on certain criteria, or to update specific rows of data depending on their current values.

2. How does the SQL Case Expression differ from the If-Then-Else structure?

The SQL Case Expression and If-Then-Else structure both allow for conditional logic in programming, but they differ significantly. The Case expression is a part of SQL language used within queries to manipulate data based on certain conditions. It’s more flexible as it can handle multiple conditions and return different results accordingly.

On the other hand, If-Then-Else is a control flow statement found in most procedural languages like Python or Java. It executes different blocks of code depending on whether a condition is true or false. However, it’s less versatile than Case when dealing with multiple conditions because nested If-Then-Else structures are needed, which can lead to complex and hard-to-read code.

3. Can you provide a detailed example of how you would use a SQL Case Expression to categorize data?

A SQL Case Expression allows for conditional logic in queries. It’s useful when categorizing data based on specific conditions.

SELECT EmployeeID, FirstName, LastName, (CASE WHEN Salary > 50000 THEN ‘High’ WHEN Salary BETWEEN 30000 AND 50000 THEN (CASE WHEN YearsExperience > 5 THEN ‘Mid-High’ ELSE ‘Mid-Low’

5. How does the SQL CASE Expression handle NULL values?

The SQL CASE expression treats NULL values as unknown. It doesn’t equate them to any value, not even another NULL. When a NULL is encountered in the WHEN clause of a CASE statement, it’s skipped and the next condition is evaluated. If no conditions are met and there’s an ELSE clause, its result is returned; if there’s no ELSE, NULL is returned. This behavior can be leveraged for handling NULLs by using IS NULL or IS NOT NULL in the WHEN clause, or coalescing NULLs to a default in the ELSE clause.

6. What is the difference between simple and searched CASE Expressions? Can you provide an example of each?

A simple CASE expression in SQL compares an expression to a set of expressions to determine the result. It’s similar to using multiple IF-THEN statements in programming languages. For example:

SELECT ProductName, CASE CategoryID WHEN 1 THEN ‘Beverage’ WHEN 2 THEN ‘Condiment’

7. How would you use a CASE Expression in a WHERE clause? Provide a sample SQL statement.

A CASE expression in a WHERE clause can be used to conditionally filter data. It allows for complex logical tests and returns different values based on the test outcome.

Here’s an example of how it could be implemented:

SELECT CustomerID, CASE WHEN SUM(PurchaseAmount) > 10000 THEN ‘High’ WHEN SUM(PurchaseAmount) BETWEEN 5000 AND 10000 THEN ‘Medium’ ELSE ‘Low’

10. How would you use a CASE Expression in an ORDER BY clause? Provide a sample SQL statement.

A CASE expression in an ORDER BY clause allows for custom sorting of results. It’s used to change the order based on a condition. For example, if we have a table ‘Employees’ with columns ‘Name’, ‘Position’ and ‘Salary’. We want to sort by position but prioritize ‘Manager’ over others.

Here is a sample SQL statement:

13. What are the limitations or pitfalls of using SQL Case Expressions?

SQL Case Expressions, while useful, have limitations. They can’t return different data types in the THEN clause; all must be implicitly convertible to a single type. This restricts flexibility. Also, they don’t support ELSE IF structure directly, making complex conditions challenging. Performance issues arise when used in WHERE clauses as indexes may not be utilized effectively, leading to slower query execution. Additionally, CASE expressions are evaluated sequentially from top to bottom and once a condition is met, it exits, ignoring subsequent WHEN statements. This could lead to unexpected results if not carefully structured. Lastly, there’s no guarantee that SQL Server will short-circuit a CASE expression, potentially causing errors with NULL values.

14. Can you create a SQL statement using a Case Expression inside a JOIN statement?

Yes, a SQL statement can incorporate a Case Expression within a JOIN statement. This is useful when you need to conditionally join tables based on certain criteria. Here’s an example:

CREATE FUNCTION case_example(@input INT) RETURNS VARCHAR(20) AS BEGIN DECLARE @result VARCHAR(20)

16. Can you write a complex case expression for filtering records based on multiple conditions?

Yes, a complex case expression can be written in SQL to filter records based on multiple conditions. Here’s an example:

SELECT OrderID, Quantity, Price, (CASE WHEN Quantity > 100 THEN Price * 0.9 ELSE Price

19. Can you explain the difference between a CASE statement and a CASE expression in SQL?

A CASE expression in SQL is used to create conditional expressions within a query. It allows for the return of values based on certain conditions, similar to an IF-THEN-ELSE statement in other programming languages.

On the other hand, a CASE statement is not native to SQL and does not exist as a standalone construct. Instead, it’s often confused with the CASE expression or misnamed when referring to control flow statements in procedural SQL extensions like PL/SQL (Oracle) or T-SQL (Microsoft), where it functions similarly to switch-case constructs in other languages.

20. How can you use a Case Expression to transpose rows into columns?

A CASE expression in SQL can be used to transpose rows into columns by creating a new column for each unique row value. This is done by using the CASE statement within the SELECT clause of your query.

For example, consider a table ‘Orders’ with columns ‘OrderID’, ‘CustomerID’, and ‘Product’. If we want to create a new column for each unique product, we would use a CASE expression like this:

SELECT COUNT(CASE WHEN condition THEN 1

22. Can you present a scenario where you would prefer DECODE over CASE Expression?

DECODE is preferable over CASE expression in scenarios where we need to perform simple equality checks. For instance, when translating codes into descriptions or performing transformations on a single column’s values. DECODE has a simpler syntax for these tasks and can be more readable.

Consider an example of a student grading system. If we want to translate numerical grades into letter grades (A, B, C, D, F), using DECODE would look like this:

In contrast, the equivalent CASE expression would be longer and potentially less clear:

SELECT column1, column2, CASE WHEN column2 = 0 THEN ‘Error: Division by Zero’ ELSE column1 / column2

24. Can you write a SQL query using a Case Expression to return values from multiple columns based on certain conditions?

Yes, a SQL query using a Case Expression can return values from multiple columns based on certain conditions. Here’s an example:

` SELECT EmployeeID, FirstName, LastName, CASE WHEN Salary > 50000 THEN ‘High’ WHEN Salary BETWEEN 30000 AND 50000 THEN ‘Medium’ ELSE ‘Low’

25. How would you tackle performance issues while using multiple Case Expressions?

To tackle performance issues with multiple Case Expressions in SQL, it’s crucial to optimize the query. One way is by reducing the number of case expressions used. If possible, combine similar conditions into one expression or use ELSE for default cases. Another method is indexing columns involved in the CASE statement. This can significantly speed up the search process. Also, consider using temporary tables for complex queries. They store intermediate results and reduce computation time. Lastly, ensure your database statistics are updated regularly as outdated stats may lead to inefficient execution plans.

Top 25 Binomial Distribution Interview Questions and Answers

Top 25 jquery events interview questions and answers, you may also be interested in..., top 25 bayesian inference interview questions and answers, top 25 android accessibility interview questions and answers, top 25 amazon ec2 interview questions and answers, top 25 qml interview questions and answers.

Data With Danny

This is serious sql.

Start your guided data apprenticeship today

Serious SQL

Your complete SQL learning experience

• Health analytics
• Marketing analytics
• People analytics
• Financial markets
• Fast moving consumer goods
• Digital marketing Topics Covered Cover many core SQL skills and techniques required for data analysis from beginner to advanced levels:
• Where filters and ordering data
• Group by aggregates
• Identifying and dealing with duplicate data
• Common table expressions and subqueries
• Summary statistics
• Exploratory data analysis
• Complex table joins
• Entity relationship diagrams
• SQL reverse engineering
• Data problem solving techniques
• Window functions
• Case When Statements
• Recursive CTEs
• Cumulative aggregates
• Simple, weighted and exponential moving metrics
• Historical vs Snapshot data analysis techniques
• Temp tables and views
• String transformations
• Regular Expressions
• Datetime manipulation This course consists of detailed technical coding tutorials, a step-by-step setup guide, recorded live training videos and access to the datasets for all case studies. Focus on learning fundamental SQL skills and understanding data at a deep level using PostgreSQL 16. Gain hands-on practical experience so you can feel confident solving challenging data problems in any database environment. Get access to our members only Discord community for further support. Additional Bonus Content
• Gain familiarity with popular programming tools such as Docker, Markdown, GitHub and the command line interface (CLI)
• Access to all 8 Week SQL Challenge case studies with further explanations and debugging exercises For further $20 student discount please reach out directly to [email protected] using your student email or share your student details for verification!

Course Curriculum

Introduction

Welcome to Serious SQL (Video)

Welcome to Serious SQL

Course Outline

SQL Environment Setup

Data Exploration

Select & Sort Data (Video)

Select & Sort Data

Record Counts & Distinct Values (Video)

Record Counts & Distinct Values

Identifying Duplicate Records (Video)

Identifying Duplicate Records

Summary Statistics (Video)

Summary Statistics

Distribution Functions (Video)

Distribution Functions

Summary (Video)

Health Analytics Mini Case Study (Video)

Health Analytics Mini Case Study

Case Study Quiz

Marketing Analytics Case Study

Case Study Introduction (Video)

Case Study Introduction

Case Study Overview (Video)

Case Study Overview

Understanding the Data (Video)

Understanding the Data

SQL Reverse Engineering (Video)

SQL Reverse Engineering

Introduction to Table Joins (Video)

Introduction to Table Joins

Joining Multiple Tables (Video)

Joining Multiple Tables

SQL Problem Solving (Video)

SQL Problem Solving

Window Functions (Video)

Window Functions

Final SQL Scripting Solution (Video)

Final SQL Scripting Solution

Marketing Analytics Quiz

Optional Window Functions Quiz

People Analytics Case Study

Creating Reusable Data Assets (Video)

Creating Reusable Data Assets

Snapshot and Historic Data (Video)

Snapshot and Historic Data

Final Case Study Solution (Video)

Final Case Study Solution

Quiz 1: Current Employee Analysis

Quiz 2: Employee Churn

Quiz 3: Management Analysis

Additional SQL Techniques

String Transformations (Video)

String Transformations

Date & Time Conversions

Serious SQL Live Training

8 week sql challenge.

Case Study #1 - Danny's Diner

Case Study #2 - Pizza Runner

Case Study #3 - Foodie-Fi

Case Study #4 - Data Bank

Case Study #5 - Data Mart

Case Study #6 - Clique Bait

Case Study #7 - Balanced Tree

Case Study #8 - Fresh Segments

Bonus Content

Linux Command Line Crash Course

GitHub Crash Course

8 Week SQL Challenge

Start your SQL learning journey today!

Case Study #1 - Danny's Diner

May 1, 2021

Case Study #2 - Pizza Runner

May 4, 2021

Case Study #3 - Foodie-Fi

May 18, 2021

Case Study #4 - Data Bank

June 1, 2021

Case Study #5 - Data Mart

June 20, 2021

Case Study #6 - Clique Bait

June 29, 2021

Case Study #7 - Balanced Tree Clothing Co.

July 2, 2021

Case Study #8 - Fresh Segments

July 9, 2021

Practice Interview Questions

SQL Tutorial

• Intermediate SQL
• Advanced SQL
• ADVANCED INTRO 🥵
• CTE vs. SUBQUERY 🥊
• WINDOW FUNCTION🪟
• SQL RANKING 🥇
• SQL LEAD LAG 🐢
• SQL SELF-JOINS 🤝
• SQL UNION 🙏
• WRITE CLEAN SQL 🧼
• EXECUTION ORDER ↕️
• SQL PIVOTING 🔨
• STRING FUNCTIONS 🔤
• INSTACART SQL CASE 🥕

Instacart SQL Data Analytics Case Study

Let's apply all we've learned across the 30+ past SQL lessons to a real-world case study where we'll analyze data from Instacart. While there's no one correct solution to this open-ended Data Analytics problem, we've included a few sample SQL queries to help you get started.

Case Study Background: About Instacart

Instacart is a grocery delivery and pickup service. Users can select items from local grocery stores through the Instacart app or website, and then either have them delivered to their doorstep by a personal shopper or prepared for pickup at the store.

For our non-North American friends working on this case study, Instacart is similar to India's Blinkit, Swiggy Instamart, or Dunzo app. In Europe, the comparable app is Getir and Gorrilas. In Latin America, Rappi serves a similar use case.

Case Study Background: The Data Analysis Task

You’re a data analyst at Instacart. Aside from the discounted groceries, you also get the benefit of solving interesting data problems.

In your 1-1 call this morning, your manager tells you that leadership wants to analyze the Instacart market data over time, to understand how the business is changing or staying the same.

Unfortunately, data engineering found some logging errors in the pipeline, and there are currently no date fields in the market data tables 🙃.

Your manager has a call with engineering tomorrow to work on fixing this so the team can track changes more closely in the future. But you’re already mid-way through Q3 and the data pipeline can’t be refreshed again until Q4. So for now, you’re stuck with the data you have.

The Task: find a way to understand how Instacart's business changed over time…without using explicit dates!

Before you panic about time-series data analysis without time-series data, take a deep breath, it's all going to be okay. Take a look at the data you have access to... things will start to click!

Case Study Background: Instacart Grocery Orders Data

Here are the schemas for all 5 tables in the Instacart market data. You’ll decide which ones are relevant and how to best use them throughout this case study.

: this table specifies which products were purchased in each Instacart order.

• The 'reordered' field indicates that the customer has a previous order that contains the product.
• Some orders will have no reordered items
• None of these fields are unique to this table, but the combination of and is unique!

: this table contains previous order contents for all customers. This data was collected in Q2, verus which is data from the current quarter (Q3).

Note: the table has the same exact schema as . You'll likely want to compare these two tables!

: info about each item in the Instacart product catalog

: info about each department

: info about each aisle in a grocery store

Before you make any assumptions, explore the tables below and make sure you understand their structures. You can explore the Instacart data here !

Your Turn: Start Analyzing with SQL!

Given the above data and the task mentioned earlier, go to town and come up with a solution. There's no more instructions, rules, or constraints, go nuts!

Just remember – it's not really about finding the “right” answer; it's about finding some business insights that you can defend with confidence!

Our Solution

Here's how we'd approach this ambiguous data case study. Feel free to follow this approach, or adapt it to derive your own insights!

Our Solution: High-Level Overview

• Identify the two specific tables you should focus on to understand broad trends over time.
• Consider a phenomenon in the data that may have changed over time. Choose something practical that you can highlight to your manager.
• Define in plain ol' English how you plan to investigate the data to solve the data analysis task.
• Express that approach in SQL.
• If your observed phenomenon has changed over time, develop 2 or more hypotheses to explain potential causes of that change. If your observed phenomenon has not changed over time, develop 2 or more hypotheses to explain why this phenomenon has remained stagnant.
• Consider other relevant factors aside from just the Instacart data, such as food trends, seasonality, supply chain, etc.

BONUS: Based on your hypotheses, write a recommendation to leadership explaining how they should either:

• Support this phenomenon if it’s helpful to Instacart business, or
• Combat this phenomenon if it’s harmful to Instacart business.

If you want to check your work against our solution, scroll down to check the answer key below.

Remember, a successful case study simply means you developed a coherent process with a data-driven conclusion and defended your method! It doesn’t have to be the same as ours; it just needs to be similarly rigorous!

Our Solution: Analyzing Prior vs. Current Products.

The two tables we want to focus on are and .

As a data analyst, one surprising event you might want to investigate using SQL could be a sudden and significant change in the reordering behavior of certain products in the current orders compared to their behavior in the prior orders.

Specifically, you could look for products that were not frequently reordered in the past (based on data from ) but are now being reordered more frequently in the current orders (from ).

We can formulate the following query to investigate:

This query joins the prior and current orders tables to the product, department, and aisles tables. The crux of the query lies in the SUMs, which allow us to compare product reorders across the prior and current orders tables. We select a lot of other fields so that we can use them to aggregate later as needed.

Finally, in the HAVING clause, we filter on products that were previously reordered fewer than 10 times, and are currently reordered 10 or more times. 10 is a nice round number to start with but a different number (or a measure of percent change) would work for this methodology too.

You can execute the query here:

The first few rows of results should look like this:

Our Solution: Analyzing Changes in Reorders By Department, Aisle

Looking at this granular view won’t give us a broad picture of changes over time, so let’s do some summarizing. We can wrap the above query in a CTE (Common Table Expression) called and do some quick aggregation. We can aggregate by department, which shows that the majority of products with increased reorders fall under Produce.

We can aggregate by aisle to see if that data tells the same story:

According to the results, most of these products come from aisles that are related to produce (fresh vegetables, fresh fruit, and packaged produce).

Our Solution: Hypothesis Behind Re-order Change

Now it’s time for the actionable part of our work: figuring out what this data means for the company, and what we'll do because of the data.

We’ll start by developing some hypotheses to explain potential reasons for the increase in reorders of produce items.

First of all, we mentioned that it’s currently Q3 , which means we’re somewhere between June and August in this scenario. Fruits and vegetables might become more popular in the summer months due to their freshness and nutritional value. So we’ll call hypothesis #1 seasonality.

It’s possible that limited-time discounts, bundle deals, or loyalty programs could drive higher reorders. We should check in with the marketing department and see if there were any deals or discounts related to produce recently. We’ll call hypothesis #2 deals.

We could also take a more skeptical approach to things and see if these products were even available before, or if a recent increase in supply chain activity has allowed for more reorders. When we see reorders go from 0 to 30 for the Organic Navel Orange, we have to ask if the conditions around those orders have changed at all. We’ll call hypothesis #3 availability.

We could keep going, but with the 3 hypotheses below, we’ve covered step 5 of the strategy framework:

• Seasonality: Produce is more popular in the summer due to its freshness and nutritional value.
• Deals: Discounts, bundle deals, or loyalty programs around produce could drive higher reorders.
• Availability: Recent shifts in supply chain and availability may have influenced consumers’ ability to buy produce.

Our Solution: Our Business Recommendation

Here is the bonus recommendation informed by our hypotheses:

After analyzing our current order data compared to previous order data, we discovered that the reorder rates for produce have significantly increased across both departments and aisles. Increased produce sales are beneficial for the company, so leadership should capitalize on customers’ higher propensity to reorder produce during the summer months.

If not already implemented, marketing should consider promoting bundle deals on produce to incentivize new buyers, who may then become repeat customers for those products. Additionally, team members working with suppliers and grocers should ensure the consistent availability of popular produce items, including Organic Navel Oranges, Russet Potatoes, and Cantaloupes, in order to maintain high reorder rates.

Interview Questions

Career resources.