Q#92: Most expensive grocery items sold by location

Unaffordable Groceries

You are given the below tables, showing Store, Product, and Sales information for a chain of grocery stores. The columns are labeled in such a way that you should be able to interpret what each field is showing.

Store

Product

Sales

Using the tables above, write a SQL query to return the most expensive product shown in each location.

Your output should return the following columns:

Where each row represents a single location and that location’s most expensive product sold.

TRY IT YOURSELF

ANSWER

In this challenge we are asked to use SQL to find the most expensive product sold in each store location using the given tables: Store, Product, and Sales. This scenario is a common problem in data analysis and can provide valuable insights into sales trends and customer preferences.

Understanding the Data

Before we dive into the SQL query, let’s understand the structure of the tables:

1. Store: This table contains information about the store locations, including store_id and location.
2. Product: This table provides details about different products available in the stores, including product_id, product_name, and price_usd.
3. Sales: This table captures sales transactions, including sale_id, product_id, store_id, and date.

The Goal

Our goal is to retrieve the most expensive product sold in each store location. To achieve this, we need to join the Product and Sales tables based on the product_id and then group the results by location. For each group, we'll select the product with the highest price.

The SQL Query

Here’s the SQL query that accomplishes this task:

SELECT
    S.location,
    P.product_name,
    P.price_usd
FROM
    (
        SELECT
            St.location,
            MAX(Pr.price_usd) AS max_price
        FROM
            Sales Sa
            JOIN Store St ON Sa.store_id = St.store_id
            JOIN Product Pr ON Sa.product_id = Pr.product_id
        GROUP BY
            St.location
    ) AS MaxPrices
    JOIN Sales Sa ON Sa.store_id = Store.store_id
    JOIN Product Pr ON Sa.product_id = Pr.product_id
    JOIN Store St ON Sa.store_id = St.store_id AND Pr.price_usd = MaxPrices.max_price
ORDER BY
    St.location;

Breaking Down the Query

1. Subquery (MaxPrices): This subquery calculates the maximum price (max_price) of products sold in each location by joining the Sales, Store, and Product tables and grouping by location.
2. Main Query: The main query then joins the MaxPrices subquery with the original Sales, Store, and Product tables. It ensures that only the rows where the product's price matches the maximum price for that location are selected.
3. Result Columns: The query selects the location, product_name, and price_usd columns for the final result.

Plug: Checkout all my digital products on Gumroad here. Please purchase ONLY if you have the means to do so. Use code: MEDSUB to get a 10% discount!

Tips and Donations