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
Which items are purchased together
Which items are purchased together?
Co-occurrence SQL Query
Interactive filteringMatrix visualizationForce layout visualization
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

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more