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
Oct 5, 2022
9 stars
Which items are purchased together
Which items are purchased together?Co-occurrence SQL Query
Interactive filtering
Matrix visualizationForce layout visualization
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
cooccurrences
Insert cell
Inputs.table(cooccurrences)
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Inputs.table(search)
Insert cell
Insert cell
Insert cell
viewof selected = Inputs.table(items, {
height: 140, // customize the height
multiple: false, // enable selecting only a single item
value: items[0] // defualt to selecting the first item
})
Insert cell
Insert cell
selected // the value of the selected
Insert cell
Insert cell
Insert cell
Insert cell
Inputs.table(searchedLinks.map(d => {
return {
item: d.item_a === selected.name ? d.item_b : d.item_a,
count: d.count,
"percent of total": Math.floor(100*d.count/selected.count) + "%"
}
}))
Insert cell
Insert cell
import { db } from "@observablehq/ecommerce-analytics-demo-data"
Insert cell
Insert cell
viewof threshold = Inputs.range([0, 100], {label: "Count threshold", step: 1, value: 5})
Insert cell
threshold
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 > ${threshold}
ORDER BY count ASC
Insert cell
Insert cell
viewof startDate = Inputs.date({label: "Start Date", value: "2020-11-01"})
Insert cell
viewof endDate = Inputs.date({label: "End Date", value: d3.utcDay.offset(startDate, 28) })
Insert cell
Insert cell
dateFormat = d3.utcFormat("%Y-%m-%d")
Insert cell
Showing results for items purchased together between ${dateFormat(startDate)} and ${dateFormat(endDate)}
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'
AND date > ${startDate}
AND date < ${endDate}
)

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 > ${threshold}
ORDER BY count ASC
Insert cell
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