The avatar for @observablehq
Observable
The end-to-end solution for building and hosting better data apps, dashboards, and reports.
Public
Which items are purchased together
Edited
Dec 6, 2022
Importers
5 stars
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
Insert cell
db
SELECT * FROM events
JOIN items ON events.item_id = items.id
WHERE type = 'purchase'
Insert cell
Insert cell
Insert cell
db
with purchased_items as (
SELECT * FROM events
JOIN items ON events.item_id = items.id
WHERE type = 'purchase'
AND ga_session_id = 16909 -- try 15963 for a more complex example
)

SELECT
a.name as item_a,
b.name as item_b
FROM purchased_items a
JOIN purchased_items b
ON a.ga_session_id = b.ga_session_id
Insert cell
Insert cell
db
with purchased_items as (
SELECT * FROM events
JOIN items ON events.item_id = items.id
WHERE type = 'purchase'
AND ga_session_id = 16909 -- try 15963 for a more complex example
)

SELECT
a.name as item_a,
b.name as item_b
FROM purchased_items a
JOIN purchased_items b
ON a.ga_session_id = b.ga_session_id
WHERE a.name < b.name
Insert cell
Insert cell
db
-- join the item details with the purchase events and save the results in a CTE
with purchased_items as (
SELECT * FROM events
JOIN items ON events.item_id = items.id
WHERE type = 'purchase'
)

SELECT
a.name as item_a,
b.name as item_b,
count(*)::INT as count
-- the central trick to this query is joining the table on itself,
-- creating a pair for each combination of rows that have the same session id
FROM purchased_items a
JOIN purchased_items b
ON a.ga_session_id = b.ga_session_id

-- we only want one record per pair, if we have a+b we dont want b+a
WHERE a.name < b.name
-- group by lets us count all the pairs
GROUP BY a.name, b.name
-- this limits our results to pairs with more than 5 occurrences
HAVING count > 5
ORDER BY count DESC
Insert cell
Insert cell
db
with purchased_items as (
SELECT * FROM events
JOIN items ON events.item_id = items.id
WHERE type = 'purchase'
)
select
name,
count(*)::INT as count,
sum(price_in_usd)::INT as revenue,
FIRST(category) as category
from purchased_items
group by name, category
order by count desc
Insert cell
Insert cell
collection
Insert cell
notebooks
Insert cell
Insert cell
import { navigation, previews, notebooks, collection} from "@observablehq/which-items-are-purchased-together"
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more