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.
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.
SELECT * FROM 'pos_operator_logs.csv'
LIMIT 100;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.
SELECT
COUNT(CASE WHEN t_cash THEN 1 END) as no_of_cash_transactions,
COUNT(CASE WHEN t_card THEN 1 END) as no_of_card_transactions
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.
SELECT
AVG(CASE WHEN t_cash AND NOT(t_card) THEN amount END) as avg_cash_transaction,
AVG(CASE WHEN t_card AND NOT(t_cash) THEN amount END) as avg_card_transaction
FROM 'pos_transactions.csv'SELECT
NOT(t_cash), t_cash
FROM 'pos_transactions.csv' LIMIT 10