case study of sql

DEV Community

Posted on Oct 12, 2022

8 Week SQL Challenge: Case Study #2 Pizza Runner

Introduction

Danny was scrolling through his Instagram feed when something really caught his eye — “80s Retro Styling 🎸 and Pizza 🍕 Is The Future!”

Danny was sold on the idea, but he knew that pizza alone was not going to help him get seed funding to expand his new Pizza Empire — so he had one more genius idea to combine with it — he was going to Uberize it — and so Pizza Runner was launched!

Danny started by recruiting “runners” to deliver fresh pizza from Pizza Runner Headquarters (otherwise known as Danny’s house) and also maxed out his credit card to pay freelance developers to build a mobile app to accept orders from customers.

Table Relationship

• customer_orders — Customers’ pizza orders with 1 row each for individual pizza with topping exclusions and extras, and order time.
• runner_orders — Orders assigned to runners documenting the pickup time, distance and duration from Pizza Runner HQ to customer, and cancellation remark. runners — Runner IDs and registration date
• pizza_names — Pizza IDs and name
• pizza_recipes — Pizza IDs and topping names
• pizza_toppings — Topping IDs and name

Case Study Questions

This case study has LOTS of questions — they are broken up by area of focus including:

A. Pizza Metrics

B. runner and customer experience, c. ingredient optimisation, d. pricing and ratings.

• E. Bonus DML Challenges (DML = Data Manipulation Language)

Data Cleaning and Transformation

Before I start with the solutions, I investigate the data and found that there are some cleaning and transformation to do, specifically on the

• null values and data types in the customer_orders table
• null values and data types in the runner_orders table
• Alter data type in pizza_names table

Firstly, to clean up exclusions and extras in the customer_orders — we create TEMP TABLE #customer_orders and use CASE WHEN.

Then, we clean the runner_orders table with CASE WHEN and TRIM and create TEMP TABLE #runner_orders .

In summary,

• pickup_time — Remove nulls and replace with ‘ ‘
• distance — Remove ‘km’ and nulls
• duration — Remove ‘minutes’ and nulls
• cancellation — Remove NULL and null and replace with ‘ ‘

Then, we alter the date according to its correct data type.

• pickup_time to DATETIME type
• distance to FLOAT type
• duration to INT type

Now that the data has been cleaned and transformed, let’s move on solving the questions! 😉

How many pizzas were ordered?

• Total pizzas ordered are 14.
• How many unique customer orders were made?

• There are 10 unique customer orders made.
• How many successful orders were delivered by each runner?

• Runner 1 has 4 successful delivered orders.
• Runner 2 has 3 successful delivered orders.
• Runner 3 has 1 successful delivered order.
• How many of each type of pizza was delivered?

• There are 9 delivered Meatlovers pizzas.
• There are 3 delivered Vegetarian pizzas.
• How many Vegetarian and Meatlovers were ordered by each customer?

• Customer 101 ordered 2 Meatlovers pizzas and 1 Vegetarian pizza.
• Customer 102 ordered 2 Meatlovers pizzas and 2 Vegetarian pizzas.
• Customer 103 ordered 3 Meatlovers pizzas and 1 Vegetarian pizza.
• Customer 104 ordered 1 Meatlovers pizza.
• Customer 105 ordered 1 Vegetarian pizza.
• What was the maximum number of pizzas delivered in a single order?

• Maximum number of pizza delivered in a single order is 3 pizzas.
• For each customer, how many delivered pizzas had at least 1 change and how many had no changes?

• Customer 101 and 102 likes his/her pizzas per the original recipe.
• Customer 103, 104 and 105 have their own preference for pizza topping and requested at least 1 change (extra or exclusion topping) on their pizza.
• How many pizzas were delivered that had both exclusions and extras?

• Only 1 pizza delivered that had both extra and exclusion topping. That’s one fussy customer!
• What was the total volume of pizzas ordered for each hour of the day?

• Highest volume of pizza ordered is at 13 (1:00 pm), 18 (6:00 pm) and 21 (9:00 pm).
• Lowest volume of pizza ordered is at 11 (11:00 am), 19 (7:00 pm) and 23 (11:00 pm).
• What was the volume of orders for each day of the week?

• There are 5 pizzas ordered on Friday and Monday.
• There are 3 pizzas ordered on Saturday.
• There is 1 pizza ordered on Sunday.

How many runners signed up for each 1 week period? (i.e. week starts 2021-01-01)

• On Week 1 of Jan 2021, 2 new runners signed up.
• On Week 2 and 3 of Jan 2021, 1 new runner signed up.
• What was the average time in minutes it took for each runner to arrive at the Pizza Runner HQ to pickup the order?

• The average time taken in minutes by runners to arrive at Pizza Runner HQ to pick up the order is 15 minutes.
• Is there any relationship between the number of pizzas and how long the order takes to prepare?

• On average, a single pizza order takes 12 minutes to prepare.
• An order with 3 pizzas takes 30 minutes at an average of 10 minutes per pizza.
• It takes 16 minutes to prepare an order with 2 pizzas which is 8 minutes per pizza — making 2 pizzas in a single order the ultimate efficiency rate.
• What was the average distance travelled for each customer?

(Assuming that distance is calculated from Pizza Runner HQ to customer’s place)

• Customer 104 stays the nearest to Pizza Runner HQ at average distance of 10km, whereas Customer 105 stays the furthest at 25km.
• What was the difference between the longest and shortest delivery times for all orders?

Firstly, let’s see all the durations for the orders.

Then, we find the difference by deducting the shortest (MIN) from the longest (MAX) delivery times.

• The difference between longest (40 minutes) and shortest (10 minutes) delivery time for all orders is 30 minutes.
• What was the average speed for each runner for each delivery and do you notice any trend for these values?

(Average speed = Distance in km / Duration in hour)

• Runner 1’s average speed runs from 37.5km/h to 60km/h.
• Runner 2’s average speed runs from 35.1km/h to 93.6km/h. Danny should investigate Runner 2 as the average speed has a 300% fluctuation rate!
• Runner 3’s average speed is 40km/h
• What is the successful delivery percentage for each runner?

• Runner 1 has 100% successful delivery.
• Runner 2 has 75% successful delivery.

Runner 3 has 50% successful delivery (It’s not right to attribute successful delivery to runners as order cancellations are out of the runner’s control.)

I will continue with Part A, B and C soon!

What are the standard ingredients for each pizza?

What was the most commonly added extra?

What was the most common exclusion?

Generate an order item for each record in the customers_orders table in the format of one of the following:

Meat Lovers

Meat Lovers - Exclude Beef

Meat Lovers - Extra Bacon

Meat Lovers - Exclude Cheese, Bacon - Extra Mushroom, Peppers

• Generate an alphabetically ordered comma separated ingredient list for each pizza order from the customer_orders table and add a 2x in front of any relevant ingredients

For example: "Meat Lovers: 2xBacon, Beef, ... , Salami"

• What is the total quantity of each ingredient used in all delivered pizzas sorted by most frequent first?

If a Meat Lovers pizza costs $12 and Vegetarian costs $10 and there were no charges for changes — how much money has Pizza Runner made so far if there are no delivery fees? What if there was an additional $1 charge for any pizza extras?

• Add cheese is $1 extra

The Pizza Runner team now wants to add an additional ratings system that allows customers to rate their runner, how would you design an additional table for this new dataset — generate a schema for this new table and insert your own data for ratings for each successful customer order between 1 to 5.

Using your newly generated table — can you join all of the information together to form a table which has the following information for successful deliveries?

• customer_id
• runner_id - rating - order_time
• pickup_time
• Time between order and pickup
• Delivery duration
• Average speed
• Total number of pizzas
• If a Meat Lovers pizza was $12 and Vegetarian $10 fixed prices with no cost for extras and each runner is paid $0.30 per kilometre travelled — how much money does Pizza Runner have left over after these deliveries?

E. Bonus Questions If Danny wants to expand his range of pizzas — how would this impact the existing data design? Write an INSERT statement to demonstrate what would happen if a new Supreme pizza with all the toppings was added to the Pizza Runner menu?

Top comments (2)

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

• Location Uj
• Work SQL developer at Independent
• Joined May 5, 2019

I've not read the whole article (too long!) But your first two code blocks could have been achieved using ISNULL(), REPLACE() and CAST() and avoided the CASE statements and ALTER column types. Cleaner code and less steps.

• Joined Jan 15, 2022

Hi Aaron thanks for your suggestion, i'm just started MySql .

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

The Rare Art of Specialization

Muneeb Hussain - Aug 2

How Do You Name Your CSS Classes?

Jenna - Jul 21

Understanding Request Waterfalls: A Key to Optimizing Web Performance

Harshaja Agnihotri - Jul 23

React vs. Next.js: The Ultimate Guide for Modern Web Development in 2024

Vishal Yadav - Jul 26

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

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

Contains solutions for #8WeekSQLChallenge case studies https://8weeksqlchallenge.com/

sharkawy98/sql-case-studies

Folders and files.

Repository files navigation

8 weeks sql challenge.

This repository contains solutions for #8WeekSQLChallenge, they are interesting real-world case studies that will allow you to apply and enhance your SQL skills in many use cases. I used Microsoft SQL Server in writing SQL queries to solve these case studies.

Table of Contents

Sql skills gained.

• Case study 1
• Case study 2
• Case study 3
• Some interesting queries from my solutions
• Data cleaning & transformation
• Aggregations
• Ranking (ROW_NUMBER, DENSE_RANK)
• Analytics (LEAD, LAG)
• CASE WHEN statements
• UNION & INTERSECT
• DATETIME functions
• Data type conversion
• TEXT functions, text and string manipulation

Case Study #1 : Danny's Diner

My solutions

Case Study #2 : Pizza Runner

Case Study #3 : Foodie-Fi

Case Study #4 : Data Bank

Some interesting queries

Top SQL Case Study Interview Questions in 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

Many SQL case study questions will ask you to investigate correlation. 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.

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

The author has tested the case study in the classroom with thousands of students. While other SQL texts tend to use examples from many different data sets, the author has found that once students get used to one case study, they learn the material at a much faster rate.

The text begins with an introduction to the case study and trains the reader to think like the query processing engine for a relational database management system. Once the reader has a grasp of the case study then SQL programming constructs are introduced with examples from the case study. In order to reinforce concepts, each chapter has several exercises with solutions provided on the book’s website.

SQL by Example  is designed both for those who have never worked with SQL as well as those with some experience. It is modular in that each chapter can be approached individually or as part of a sequence, giving the reader flexibility in the way that they learn or refresh concepts. This also makes the book a great reference to refer back to once the reader is honing his or her SQL skills on the job.

8 Week SQL Challenge

Start your SQL learning journey today!

• Case Study #5 - Data Mart

Danny Ma · June 20, 2021

Introduction

Data Mart is Danny’s latest venture and after running international operations for his online supermarket that specialises in fresh produce - Danny is asking for your support to analyse his sales performance.

In June 2020 - large scale supply changes were made at Data Mart. All Data Mart products now use sustainable packaging methods in every single step from the farm all the way to the customer.

Danny needs your help to quantify the impact of this change on the sales performance for Data Mart and it’s separate business areas.

The key business question he wants you to help him answer are the following:

• What was the quantifiable impact of the changes introduced in June 2020?
• Which platform, region, segment and customer types were the most impacted by this change?
• What can we do about future introduction of similar sustainability updates to the business to minimise impact on sales?

Available Data

For this case study there is only a single table: data_mart.weekly_sales

The Entity Relationship Diagram is shown below with the data types made clear, please note that there is only this one table - hence why it looks a little bit lonely!

Column Dictionary

The columns are pretty self-explanatory based on the column names but here are some further details about the dataset:

• Data Mart has international operations using a multi- region strategy
• Data Mart has both, a retail and online platform in the form of a Shopify store front to serve their customers
• Customer segment and customer_type data relates to personal age and demographics information that is shared with Data Mart
• transactions is the count of unique purchases made through Data Mart and sales is the actual dollar amount of purchases

Each record in the dataset is related to a specific aggregated slice of the underlying sales data rolled up into a week_date value which represents the start of the sales week.

Example Rows

10 random rows are shown in the table output below from data_mart.weekly_sales :

Interactive SQL Instance

You can use the embedded DB Fiddle below to easily access these example datasets - this interactive session has everything you need to start solving these questions using SQL.

You can click on the Edit on DB Fiddle link on the top right hand corner of the embedded session below and it will take you to a fully functional SQL editor where you can write your own queries to analyse the data.

You can feel free to choose any SQL dialect you’d like to use, the existing Fiddle is using PostgreSQL 13 as default.

Serious SQL students will have access to the same relevant schema SQL and example solutions which they can use with their Docker setup from within the course player!

Case Study Questions

The following case study questions require some data cleaning steps before we start to unpack Danny’s key business questions in more depth.

1. Data Cleansing Steps

In a single query, perform the following operations and generate a new table in the data_mart schema named clean_weekly_sales :

Convert the week_date to a DATE format

Add a week_number as the second column for each week_date value, for example any value from the 1st of January to 7th of January will be 1, 8th to 14th will be 2 etc

Add a month_number with the calendar month for each week_date value as the 3rd column

Add a calendar_year column as the 4th column containing either 2018, 2019 or 2020 values

Add a new column called age_band after the original segment column using the following mapping on the number inside the segment value

• Add a new demographic column using the following mapping for the first letter in the segment values:

Ensure all null string values with an "unknown" string value in the original segment column as well as the new age_band and demographic columns

Generate a new avg_transaction column as the sales value divided by transactions rounded to 2 decimal places for each record

2. Data Exploration

• What day of the week is used for each week_date value?
• What range of week numbers are missing from the dataset?
• How many total transactions were there for each year in the dataset?
• What is the total sales for each region for each month?
• What is the total count of transactions for each platform
• What is the percentage of sales for Retail vs Shopify for each month?
• What is the percentage of sales by demographic for each year in the dataset?
• Which age_band and demographic values contribute the most to Retail sales?
• Can we use the avg_transaction column to find the average transaction size for each year for Retail vs Shopify? If not - how would you calculate it instead?

3. Before & After Analysis

This technique is usually used when we inspect an important event and want to inspect the impact before and after a certain point in time.

Taking the week_date value of 2020-06-15 as the baseline week where the Data Mart sustainable packaging changes came into effect.

We would include all week_date values for 2020-06-15 as the start of the period after the change and the previous week_date values would be before

Using this analysis approach - answer the following questions:

• What is the total sales for the 4 weeks before and after 2020-06-15 ? What is the growth or reduction rate in actual values and percentage of sales?
• What about the entire 12 weeks before and after?
• How do the sale metrics for these 2 periods before and after compare with the previous years in 2018 and 2019?

4. Bonus Question

Which areas of the business have the highest negative impact in sales metrics performance in 2020 for the 12 week before and after period?

• demographic
• customer_type

Do you have any further recommendations for Danny’s team at Data Mart or any interesting insights based off this analysis?

This case study actually is based off a real life change in Australia retailers where plastic bags were no longer provided for free - as you can expect, some customers would have changed their shopping behaviour because of this change!

Analysis which is related to certain key events which can have a significant impact on sales or engagement metrics is always a part of the data analytics menu. Learning how to approach these types of problems is a super valuable lesson and hopefully these ideas can help you next time you’re faced with a tough problem like this in the workplace!

Ready for the next 8 Week SQL challenge case study? Click on the banner below to get started with case study #6!

Official Solutions

If you’d like to see the official code solutions and explanations for this case study and a whole lot more, please consider joining me for the Serious SQL course - you’ll get access to all course materials and I’m on hand to answer all of your additional SQL questions directly!

Serious SQL is priced at $49USD and $29 for students and includes access to all written course content, community events as well as live and recorded SQL training videos!

Please send an email to [email protected] from your educational email or include your enrolment details or student identification for a speedy response!

Community Solutions

This section will be updated in the future with any community member solutions with a link to their respective GitHub repos!

Final Thoughts

The 8 Week SQL Challenge is proudly brought to you by me - Danny Ma and the Data With Danny virtual data apprenticeship program.

Students or anyone undertaking further studies are eligible for a $20USD student discount off the price of Serious SQL please send an email to [email protected] from your education email or include information about your enrolment for a fast response!

We have a large student community active on the official DWD Discord server with regular live events, trainings and workshops available to all Data With Danny students, plus early discounted access to all future paid courses.

There are also opportunities for 1:1 mentoring, resume reviews, interview training and more from myself or others in the DWD Mentor Team.

From your friendly data mentor, Danny :)

All 8 Week SQL Challenge Case Studies

All of the 8 Week SQL Challenge case studies can be found below:

• Case Study #1 - Danny's Diner
• Case Study #2 - Pizza Runner
• Case Study #3 - Foodie-Fi
• Case Study #4 - Data Bank
• Case Study #6 - Clique Bait
• Case Study #7 - Balanced Tree Clothing Co.
• Case Study #8 - Fresh Segments

Share: Twitter , Facebook

SQL Case Study – WOWI Sales Analysis

• explore a database from scratch using documentation and guided questions
• use SQL to respond to business questions with data-driven insights
• apply SQL to real-life scenarios

• What You'll Learn
• Career Programs
• What Students Say

SQL Case Study – WOWI Sales Analysis Course Overview

You’ve recently joined World Wide Importers as a Business Intelligence Analyst. They are a novelty goods distributor and are keen to get you involved in helping make some key business decisions.

You’ll be exploring the database, responding to questions from the product and marketing teams, and using all the knowledge you gained in SQL fundamentals to test your skills on the job.

SQL Fundamentals Learning Objectives

• Explore  new data using documentation
• Apply  SQL queries to respond to business questions
• Execute  simple queries from single tables
• Execute  complex queries using joins and subqueries
• Develop  responses based on evolving requirements from the business

Who should take this course?

SQL Case Study - WOWI Sales Analysis

Approx 3h to complete

100% online and self-paced

What you'll learn

Case study introduction, basic queries, multi-table queries, qualified assessment, this course is part of the following programs.

Why stop here? Expand your skills and show your expertise with the professional certifications, specializations, and CPE credits you’re already on your way to earning.

Business Intelligence & Data Analyst (BIDA®) Certification

• Skills Learned Data visualization, data warehousing and transformation, data modeling and analysis
• Career Prep Business intelligence analyst, data scientist, data visualization specialist

Business Intelligence Analyst Specialization

• Skills learned Data Transformation & Automation, Data Visualization, Coding, Data Modeling
• Career prep Data Analyst, Business Intelligence Specialist, Finance Analyst, Data Scientist

What Our Members Say

Linda etuhole nakasole, kelvin nzyoka, mariam ben nasr, onyedika nwoji, oluwafemi ayelomi, lydia angolo endjala, farooq anwer, robert muwanga, mandy udeani, tinashe hemish katanda, christoforos contorozis, kailas kasar, chukwudi nwankwo, frequently asked questions.

Create a free account to unlock this Template

Access and download collection of free Templates to help power your productivity and performance.

Already have an account? Log in

Supercharge your skills with Premium Templates

Take your learning and productivity to the next level with our Premium Templates.

Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.

Already have a Self-Study or Full-Immersion membership? Log in

Access Exclusive Templates

Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.

Already have a Full-Immersion membership? Log in

• SQL Server training
• Write for us!

A case study of SQL Query tuning in SQL Server

Gaining experience in SQL query tuning can be very difficult and complicated for database developers or administrators. For this reason, in this article, we will work on a case study and we are going to learn how we can tune its performance step by step. In this fashion, we will understand well how to approach query performance issues practically.

Pre-requirements

In this article, we will use the Adventureworks2017 sample database. At the same time, we will also use the Create Enlarged AdventureWorks Tables script to obtain an enlarged version of the SalesOrder and SalesOrderDetail tables because the size of this database is not sufficient to perform performance tests. After installation of the Adventureworks2017 database, we can execute the table enlarging script.

Case Study: SQL Query tuning without creating a new index

Imagine that, you are employed as a full-time database administrator in a company and this company is still using SQL Server 2017 version. You have taken an e-mail from the software development team and they are complaining about the following query performance in their e-mail.

Your objective is to improve the performance of the above query without creating a new index on the tables but you can re-write the query.

The first step of the SQL query tuning: Identify the problems

Firstly, we will enable the actual execution plan in the SSMS and execute the problematic query. Using the actual execution plan is the best approach to analyze a query because the actual plan includes all accurate statistics and information about a query. However, if a query is taking a long time, we can refer to the estimated execution plan. After this explanation, let’s examine the select operator of the execution plan.

• Interpreting execution plans of T-SQL queries
• Main Concepts of SELECT operators in SQL Server execution plans

The ElapsedTime attribute indicates the execution time of a query and we figure out from this value that this query is completed in 142 seconds. For this query, we also see the UdfElapsedTime attribute and it indicates how long the database engine deal to invoke the user-defined functions in the query. Particularly for this query, these two elapsed times are very close so we can deduce that the user-defined function might cause a problem.

Another point to take into consideration for this query is parallelism. For this query, the Estimated Subtree Cost value exceeds the Cost Threshold for Parallelism  setting of the server but the query optimizer does not generate a parallel execution plan because of the scalar function. The scalar functions prevent the query optimizer to generate a parallel plan.

The last problem with this query is the TempDB spill issue and this problem is indicated with the warning signs in the execution plan.

Outdated statistics, poorly written queries, ineffective index usage might be caused to tempdb spill issues.

Improve performance of the scalar-function in a query

The scalar-functions can be a performance killer for the queries, and this discourse would be exactly true for our sample query. Scalar-functions are invoked for every row of the result set by the SQL Server. Another problem related to the scalar-functions is the black box problem because the query optimizer has no idea about the code inside the scalar-function, due to this issue the query optimizer does not consider the cost impact of the scalar functions on the query.

A new feature has been announced with SQL Server 2019 and can help overcome most of the performance issues associated with scalar functions. The name of this feature is Scalar UDF Inlining in SQL Server 2019 . On the other hand, if we are using earlier versions of SQL Server, we should adapt the scalar function code explicitly to the query if it is possible. The common method is to transform the scalar-function into a subquery and implement it to query with the help of the CROSS APPLY operator. When we look at the inside of the ufnGetStock function, we can see that it is summing the quantity of products according to the ProductId only a specific LocationId column.

We can transform and implement the ufnGetStock scalar-function as shown below. In this way, we ensure that our sample query can run in parallel and will be faster than the first version of the query.

This query has taken 71 seconds to complete but when we look at the execution plan, we see a parallel execution plan. However, the tempdb spill issue is persisted. This case obviously shows that we need to expend more effort to overcome the tempdb spill problem and try to find out new methods.

Think more creative for SQL query tuning

To get rid of the tempdb spill issue, we will create a temp table and insert all rows to this temporary table. The temporary tables offer very flexible usage so we can add a computed column instead of the LEN function which is placed on the WHERE clause. The insert query will be as below.

When we analyze this query we can see the usage of the TABLOCK hint after the INSERT statement. The usage purpose of this keyword is to enable a parallel insert option. So that, we can gain more performance. This situation can be seen in the execution plan.

In this way, we have inserted the 1.286.520 rows into the temporary table in just one second. However, the temporary table still holds more data than we need because we haven’t filtered the CreditCard ApprovalCode column values ​​with character lengths are greater than 10 in the insert operation. At this point, we will make a little trick and delete the rows whose are character length smaller than 10 or equal to 10. After the insert statement, we will add the following delete statement so that we will obtain the all qualified records in the temp table.

SQL Query tuning: Using indexes to improve sort performance

When we design an effective index for the queries which include the ORDER BY clause, the execution plan does not require to sort the result set because the relevant index returns the rows in the required order. Moving from this idea, we can create a non-clustered index that satisfies sort operation requirements. The important point about this SQL query tuning practice is that we have to get rid of the sort operator and the generated index advantage should outweigh the disadvantage. The following index will be helping to eliminate sort operation in the execution plan.

Now, we execute the following query and then examine the execution plan of the select query.

As we can see in the execution plan, the database engine used the IX_Sort index to access the records and it also did not require to use of a sort operator because the rows are the sorted manner. In the properties of the index scan operator, we see an attribute that name is Scan Direction .

The scan direction attribute explains that SQL Server uses the b-tree structure to read the rows from beginning to the end at the leaf levels. At the same time, this index helps us to overcome the tempdb spill issue.

Finally, we see that the query execution time was reduced from 220 seconds to 33 seconds.

In this article, we learned practical details about SQL query tuning and these techniques can help when you try to solve a query performance problem. In the case study, the query which has a performance problem contained 3 main problems. These are:

• Scalar-function problem
• Using a serial execution plan
• Tempdb spill issue

At first, we transformed the scalar-function into a subquery and implement it to query with the CROSS APPLY operator. In the second step, we eliminated the tempdb spill problem to use a temporary table. Finally, the performance of the query has improved significantly.

• Recent Posts

• SQL Performance Tuning tips for newbies - April 15, 2024
• SQL Unit Testing reference guide for beginners - August 11, 2023
• SQL Cheat Sheet for Newbies - February 21, 2023

Related posts:

• Mapping schema and recursively managing data – Part 1
• SQL SUBSTRING function and its performance tips
• Top SQL Server Books
• Parallel Nested Loop Joins – the inner side of Nested Loop Joins and Residual Predicates
• A complete guide to T-SQL Metadata Functions in SQL Server