1. Querying our coffee dataset
In this scenario, I’ll once again take on the role of a data analyst at a large coffee retail company. Now that I have access to a variety of datasets, it’s time to start analyzing the data. I’ll begin by opening the SQL Editor to review an existing query I’ve previously run. This query joins our *stores*, *sales*, and *products* tables, providing a comprehensive view of how our stores are performing financially.
Query
SELECT
st.store_id,
s.date,
s.revenue,
s.stock,
s.price,
p.hierarchy1_id
FROM
stores st
LEFT JOIN sales s on st.store_id = s.store_id
LEFT JOIN products p on s.product_id = p.product_id
While this query is a solid starting point, I’d like to enhance it by adding some business logic. One of our key priorities is tracking low inventory levels. Using a CASE statement in Databricks SQL, I can categorize products based on their stock levels. If a product has fewer than 10 units, it will be labeled as Low Stock. For stock levels between 10 and 19, it will be marked as Medium Stock. Anything with 20 or more units will be categorized as High Stock, indicating no immediate inventory concerns.
Second query
SELECT
st.store_id,
s.date,
s.revenue,
s.stock,
s.price,
CASE
WHEN s.stock < 10 THEN 'LowStock'
WHEN s.stock >= 10 AND s.stock < 20 THEN 'MediumStock'
ELSE "HighStock"
END AS RemainingInventory,
p.hierarchy1_id
FROM
stores st
LEFT JOIN sales s on st.store_id = s.store_id
LEFT JOIN products p on s.product_id = p.product_id
With this information in hand, I’m curious to explore how our inventory levels are distributed across different product lines and how they relate to sales. To gain a clearer picture, I’ll create a visualization. Specifically, I’ll design a bar chart titled Inventory and Sales by Hierarchy. In this chart, the product hierarchy will serve as the x-axis, representing different product categories. For the y-axis, I’ll plot total sales, and I’ll group the results by the inventory categories I just defined Low Stock, Medium Stock, and High Stock.
This visualization will provide valuable insights into product demand patterns and help identify which categories may need restocking. By examining both inventory levels and sales performance side by side, I can more proactively manage inventory, ensuring that high-demand products remain in stock and avoiding potential sales losses.
This example demonstrates how we can use queries and visualizations together to extract meaningful insights from our data. In the next exercise, you’ll apply these techniques to analyze insurance claim data, building your skills in creating actionable data models and visualizations.
2. Let's practice!