Skip to content

Live training | 2023-03-14 11am EST | Supermarket Transaction Analysis in SQL | Martina Chiari

The checkout line is a familiar part of the retail experience. Any retailer needs to understand their checkout data to answer important questions like "do people spend more with card or cash?", "how many items do people buy at different times?", and "are self-service checkouts quicker than those with a cashier?".

In this live training, you'll use SQL to analyze transaction data from a Polish Supermarket to answer financial and logistical questions.

A financial analysis of point of sale transactions can have many purposes. A non-exhaustive list is:

  • increase sales by identifying what matters to the customer, is it promotions? on what items? is it opening hours? prices of particular items? and so on;
  • increasing productivity of stores and operators by identifying factors that result in Lower processing time per item or basket size, accurate predictions for labour scheduling, adequate level of inventory, fewer scanning errors at checkout, and so on;
  • predict sales in the short, medium and long term to inform decisions on investments in fixed assets and human resources, financing, go-to-market invesments, etc.

DATA: Retail operators in a grocery store supermarket located in a alarge city in Southern Poland Primary Goal: Data Exploratio with SQL for financial analyst Second Goal: Identify trends of sales volume Why?: inform business decisions through financial analysis to increase effiency and forecast future revenue and workforce requirements

Task 1: Exploring the data

A good first step in exploring a database is to select the first few rows from each table.

Instructions

Select all columns and the first 100 rows from pos_operator_logs and pos_transactions.

Spinner
DataFrameas
df
variable
SELECT *
FROM pos_operator_logs.csv
LIMIT 100
Spinner
DataFrameas
df
variable
SELECT *
FROM pos_transactions.csv
LIMIT 100

Task 2: Do more people make transactions by card or by cash?

Understanding how people pay is crucial for shop logistics like deciding what kind of checkout equipment to buy.

Instructions

Count how many transactions were processed by cash (the case when t_cash is true) and by card (the case when t_card is true). Use the pos_transactions table.

Spinner
DataFrameas
df
variable
SELECT 
    COUNT(CASE WHEN t_cash  THEN 1 END) AS CASH,
    COUNT(CASE WHEN t_card  THEN 1 END) AS CARD,
    COUNT(CASE WHEN t_cash THEN 1 END) - COUNT(CASE WHEN t_card THEN 1 END) AS Difference
FROM pos_transactions.csv;

Task 3: Do people spend more per transaction when using cash or card?

Similarly, knowing how much people spend per transaction using different payment methods is helpful for deciding on security arrangments, and marketing to encourage shoppers to use different payment methods.

Find the average per transaction type when a transaction is only card, or only cash.

Instructions

Calculate the mean (average) amount spent per transaction for cash and for card.

Spinner
DataFrameas
df
variable
SELECT 
AVG(CASE WHEN t_cash AND NOT t_card THEN amount END) AS avg_cash,
AVG(CASE WHEN t_card AND NOT t_cash THEN amount END) AS avg_card,
AVG(CASE WHEN t_card AND NOT t_cash THEN amount END) - AVG(CASE WHEN t_cash AND NOT t_card THEN amount END) AS difference
FROM pos_transactions.csv;
‌
‌
‌