Graph, vector search, and model scoring in one SQL query A MerRec case study over 1.27 billion marketplace events
MerRec contains 1.27 billion events from 5.57 million Mercari US shoppers between May and October 2023: views, likes, cart taps, offers and completed purchases across 82.6 million listings.
Every query here went over Arrow Flight SQL to one Algeon server with an
NVIDIA RTX PRO 6000 (96 GB) and read Parquet files from local NVMe. A 90 GiB
grant capped each query's device allocations. The shopper taste vectors
(mr_taste64) and the XGBoost model (propensity_xgb.json) were prepared
before the queries ran. In a deployment, the application chooses seeds,
cohorts and parameters and passes the selected shoppers on to its campaign
service.
The discovery guide shows the Barbie question as a recorded Flight SQL session.
Which 5,000 shoppers should a Pokemon campaign reach?
The campaign list holds 5,000 shoppers. One query builds candidate lists with a graph algorithm, a vector search and an externally trained model, then scores every list against the purchases that followed.
The seed is the 1,302 shoppers who bought a Pokemon listing in July or
August 2023. Eligible shoppers are the 2,079,441 others in
mr_taste64, the prepared table of 64-dimensional taste vectors for shoppers
with at least 20 views before September 1. Three stages read that table and
the raw events:
- Graph affinity.
cugraph_personalized_pagerankstarts from the Pokemon brand vertex on the user-brand like graph built from likes before September. The 20,000 eligible shoppers with the highest scores form the graph audience. - Taste lookalikes.
cuvs_brute_force_knnreturns the 20 nearest taste neighbors of each seed buyer among eligible shoppers. - Model ranking.
cuml_forest_predictscores every eligible shopper withpropensity_xgb.json, the XGBoost purchase-propensity model from the buyer shortlist example. Within each audience, the 5,000 highest scores form a shortlist.
Window functions rank the scores inside each audience in one pass, and the
final GROUP BY counts the shoppers in each list who bought a Pokemon listing
in September or October.
), affinity AS (
SELECT -(vertex + 1) AS id, value AS ppr
FROM cugraph_personalized_pagerank(
edges => (SELECT src, dst FROM likes),
personalization => (SELECT 17470 AS vertex, 1.0 AS value),
directed => false)
WHERE vertex < 0
), graph_audience AS (
SELECT id FROM (
SELECT c.id, ROW_NUMBER() OVER (ORDER BY a.ppr DESC, c.id) AS rk
FROM affinity a JOIN candidates c ON c.id = a.id) ranked
WHERE rk <= 20000
), taste_audience AS (
SELECT DISTINCT neighbor_id AS id
FROM cuvs_brute_force_knn(
dataset => (SELECT * FROM candidates ORDER BY id),
queries => (SELECT t.* FROM mr_taste64 t JOIN seed s ON s.id = t.id ORDER BY t.id),
k => 20, metric => 'l2_expanded')
), features AS (
SELECT e.user_id AS id,
CAST(SUM(CASE WHEN e.event_type = 'item_view' THEN 1 ELSE 0 END) AS REAL) AS views,
CAST(SUM(CASE WHEN e.event_type = 'item_like' THEN 1 ELSE 0 END) AS REAL) AS likes,
CAST(SUM(CASE WHEN e.event_type IN ('item_add_to_cart_tap', 'offer_make', 'buy_start') THEN 1 ELSE 0 END) AS REAL) AS intents,
CAST(SUM(CASE WHEN e.event_type = 'buy_comp' THEN 1 ELSE 0 END) AS REAL) AS buys,
CAST(SUM(CASE WHEN e.event_type = 'buy_comp' THEN i.price ELSE 0 END) AS REAL) AS gmv,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-02 00:00:00' AND e.event_type = 'item_view' THEN 1 ELSE 0 END) AS REAL) AS views_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-02 00:00:00' AND e.event_type = 'item_like' THEN 1 ELSE 0 END) AS REAL) AS likes_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-02 00:00:00' AND e.event_type IN ('item_add_to_cart_tap', 'offer_make', 'buy_start') THEN 1 ELSE 0 END) AS REAL) AS intents_30d,
CAST(SUM(CASE WHEN e.event_time >= TIMESTAMP '2023-08-02 00:00:00' AND e.event_type = 'buy_comp' THEN 1 ELSE 0 END) AS REAL) AS buys_30d,
CAST(SUM(i.price) / COUNT(*) AS REAL) AS mean_price
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
WHERE e.event_time >= TIMESTAMP '2023-06-01 00:00:00' AND e.event_time < TIMESTAMP '2023-09-01 00:00:00'
GROUP BY e.user_id
), scored AS (
SELECT id, prediction AS score
FROM cuml_forest_predict(
input => (SELECT id, LN(1 + views) AS views, LN(1 + likes) AS likes, LN(1 + intents) AS intents,
LN(1 + buys) AS buys, LN(1 + gmv) AS gmv, LN(1 + views_30d) AS views_30d,
LN(1 + likes_30d) AS likes_30d, LN(1 + intents_30d) AS intents_30d,
LN(1 + buys_30d) AS buys_30d, LN(1 + mean_price) AS mean_price
FROM features ORDER BY id),
model => 'propensity_xgb.json', model_format => 'xgboost_json')
Over Flight SQL, the first run took 8.31 s and runs 2 to 4 a median of 8.32 s. An equivalent CPU pipeline combining DuckDB, igraph, faiss and xgboost took 40.3 s on the same host, passing intermediate tables through four separate libraries across host memory.
Previous viewers buy at 0.23%, seven times the 0.033% rate of all eligible shoppers. The graph audience reaches 0.43% and the taste lookalikes 0.69%. The two lists share only 1,846 shoppers, so each method finds people the other misses. Ranking their union with the model and keeping 5,000 gives 1.86%: 93 of the pool's 195 buyers in 13% of its shoppers.
The two control rows show what the algorithms add. The model alone, applied to everyone, reaches 0.60%. Trained on any purchase, with no brand among its features, it ranks shoppers without a Pokemon signal. The viewer rule plus the model reaches 1.44%. The graph-or-taste pool plus the model reaches 21 more buyers than the rule at the same list size.
To hand the list to a campaign service, replace the final aggregation with a
projection over the ranked CTE. The timings above measure the evaluation
query, not this variant.
SELECT id AS user_id, rank_pool AS priority
FROM ranked
WHERE in_pool = 1
AND rank_pool <= 5000
ORDER BY rank_pool;
The plan had 12 native fragments and zero host DataFusion boundaries. The largest query allocator peak was 44.86 GiB of the 90 GiB query limit. The capture contains the executed SQL, query IDs, timings and every result row.
Which brands grew closer to Barbie's audience?
The query compares each brand's affinity to Barbie's audience from May through June against July through August 2023 and ranks the relative change. A later check of product names tests whether the change coincides with Barbie-related listings.
Each period has a graph of distinct user-brand likes. The after_release
flag is true for July to August likes. The split at 1 July falls 20 days
before the Barbie film opened on 21 July. User vertices are -(user_id + 1)
and brand vertices are brand_id values. Both columns are non-negative in
this data, so users occupy negative IDs and la.vertex > 0 keeps only brands
(brand 0 is excluded).
Personalized PageRank starts from Barbie; ordinary PageRank supplies each
brand's global graph score. Their ratio, lift, describes relative graph
affinity: 1 means the two scores are equal. All four calls use the default
options (alpha 0.85, epsilon 1e-5, max_iterations 100), and each
converged before the iteration limit. Lifts from the two periods were
therefore computed to one tolerance. The query ranks the change in that ratio
and requires at least 1,000 fans (distinct users) per brand in both periods.
WITH likes AS (
SELECT DISTINCT -(e.user_id + 1) AS src, CAST(i.brand_id AS BIGINT) AS dst,
e.event_time >= TIMESTAMP '2023-07-01 00:00:00' AS after_release
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
WHERE e.event_type = 'item_like' AND i.brand_id <> 0
AND e.event_time >= TIMESTAMP '2023-05-01 00:00:00'
AND e.event_time < TIMESTAMP '2023-09-01 00:00:00'
), lift_before AS (
SELECT p.vertex, p.value / r.value AS lift
FROM cugraph_personalized_pagerank(
edges => (SELECT src, dst FROM likes WHERE NOT after_release),
personalization => (SELECT 2595 AS vertex, 1.0 AS value),
directed => false) p
JOIN cugraph_pagerank(
edges => (SELECT src, dst FROM likes WHERE NOT after_release), directed => false) r
ON r.vertex = p.vertex
), lift_after AS (
SELECT p.vertex, p.value / r.value AS lift
FROM cugraph_personalized_pagerank(
edges => (SELECT src, dst FROM likes WHERE after_release),
personalization => (SELECT 2595 AS vertex, 1.0 AS value),
directed => false) p
JOIN cugraph_pagerank(
edges => (SELECT src, dst FROM likes WHERE after_release), directed => false) r
ON r.vertex = p.vertex
), fans AS (
SELECT dst AS brand_id,
SUM(CASE WHEN after_release THEN 0 ELSE 1 END) AS fans_before,
SUM(CASE WHEN after_release THEN 1 ELSE 0 END) AS fans_after
FROM likes GROUP BY dst
), brands AS (
SELECT CAST(brand_id AS BIGINT) AS brand_id, MIN(brand_name) AS brand_name
FROM merrec_items WHERE brand_id <> 0 GROUP BY brand_id
)
SELECT b.brand_name, f.fans_before, f.fans_after,
lb.lift AS lift_before, la.lift AS lift_after, la.lift / lb.lift AS lift_change
FROM lift_after la
JOIN lift_before lb ON lb.vertex = la.vertex
JOIN fans f ON f.brand_id = la.vertex
JOIN brands b ON b.brand_id = la.vertex
WHERE la.vertex > 0 AND la.vertex <> 2595
AND f.fans_before >= 1000 AND f.fans_after >= 1000
ORDER BY lift_change DESC, b.brand_name
LIMIT 25;
The first four rows are the largest increases. Taylor Swift is included as a comparison for the product-name check below.
The time filters, four graph calls and brand joins ran natively in one plan. Over Flight SQL, the first run on a fresh server took 2.49 s and runs 2 to 4 a median of 1.85 s. On the same host, with the files in the page cache, an equivalent CPU pipeline combining 32-thread DuckDB with igraph took a median of 23.3 s (2.3 s SQL extraction, 13.0 s renumbering and graph construction, 7.7 s for four PageRank calls, 0.3 s final join) with identical top-25 rankings. Device execution avoids transferring and renumbering the 23 million edge pairs across host memory.
Follow-up: check product names. The graph query did not search names.
This separate SQL counts like events on listings whose names contain
"Barbie", using the same periods. Each of the five brand names checked here
maps to one brand_id, matching the brands in the graph result.
WITH selected_items AS (
SELECT item_id, brand_name,
LOWER(COALESCE(name, '')) LIKE '%barbie%' AS is_barbie
FROM merrec_items
WHERE brand_name IN ('NYX', 'Amc', 'Primark', 'ALDO', 'Taylor Swift', 'Stanley', 'OPI', 'ZARA')
), likes AS (
SELECT i.brand_name, i.is_barbie,
e.event_time < TIMESTAMP '2023-07-01 00:00:00' AS may_june
FROM merrec_events e JOIN selected_items i ON i.item_id = e.item_id
WHERE e.event_type = 'item_like'
AND e.event_time >= TIMESTAMP '2023-05-01 00:00:00'
AND e.event_time < TIMESTAMP '2023-09-01 00:00:00'
)
SELECT brand_name,
SUM(CASE WHEN may_june THEN 1 ELSE 0 END) AS total_likes_may_june,
SUM(CASE WHEN NOT may_june THEN 1 ELSE 0 END) AS total_likes_july_august,
SUM(CASE WHEN may_june AND is_barbie THEN 1 ELSE 0 END) AS barbie_likes_may_june,
SUM(CASE WHEN NOT may_june AND is_barbie THEN 1 ELSE 0 END) AS barbie_likes_july_august
FROM likes
GROUP BY brand_name
ORDER BY brand_name;
NYX's records include "LIMITED EDITION NYX Barbie Flip Phone Mirror". Taylor Swift's graph score also increased, but only 135 of 164,499 later likes were on matching listings. Its change remains unexplained by this check. Name matches can include seller descriptions and color names.
Follow-up: change the seed. A rise in graph affinity could reflect a
broad summer trend rather than Barbie. Rerunning the query with
another seed brand tests that. Hot Wheels is a Mattel brand whose collector
audience overlaps Barbie's; Nike is a large brand with no July event. Each
control replaces Barbie's brand_id (2595) with the seed's (9877 for Hot
Wheels, 15987 for Nike) in the personalization vertex and the la.vertex <>
exclusion. The rest of the SQL is unchanged.
With Nike as the seed, none of the four brands appears among the 25 largest changes. With Hot Wheels, NYX, ALDO and Primark appear with smaller changes and Amc is absent, consistent with the Mattel collector audience sharing part of the Barbie movement. Item attributes are the latest retained values.
The graph capture contains all 25 brands, query IDs and timings. The listing capture and control captures contain the follow-up SQL and full results.
Which brands belong on the same discovery page?
The query groups brands by overlapping audiences, including combinations that cross catalog categories. It uses May to October 2023 likes: a fan is a distinct shopper who liked a brand.
SQL reduces 182,889,825 like events to user-brand pairs and selects the 5,000 brands with the most fans. Jaccard measures shared audience divided by combined audience for each brand pair. Similarities of at least 0.02 become edges for Leiden. The resulting groups join back to brand names. Leiden uses the retained connections and currently ignores edge weights.
WITH fans AS (
SELECT -(e.user_id + 1) AS src, CAST(i.brand_id AS BIGINT) AS dst
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
WHERE e.event_type = 'item_like' AND i.brand_id <> 0
GROUP BY e.user_id, i.brand_id
), top_brands AS (
SELECT dst AS brand_id, COUNT(*) AS fans FROM fans GROUP BY dst ORDER BY fans DESC, dst LIMIT 5000
), worlds AS (
SELECT vertex AS brand_id, community AS world
FROM cugraph_leiden(
edges => (
SELECT first AS src, second AS dst, CAST(similarity AS DOUBLE) AS weight
FROM cugraph_jaccard(
edges => (SELECT src, dst FROM fans),
vertex_pairs => (SELECT a.brand_id AS first, b.brand_id AS second
FROM top_brands a JOIN top_brands b ON a.brand_id < b.brand_id),
directed => false)
WHERE similarity >= 0.02),
directed => false, resolution => 3, seed => 42)
), members AS (
SELECT w.world, n.brand_name, t.fans
FROM worlds w
JOIN top_brands t ON t.brand_id = w.brand_id
JOIN (SELECT CAST(brand_id AS BIGINT) AS brand_id, MIN(brand_name) AS brand_name
FROM merrec_items WHERE brand_id <> 0 GROUP BY brand_id) n ON n.brand_id = w.brand_id
), ranked AS (
SELECT world, brand_name, fans,
ROW_NUMBER() OVER (PARTITION BY world ORDER BY fans DESC, brand_name) AS rk,
COUNT(*) OVER (PARTITION BY world) AS brands,
SUM(fans) OVER (PARTITION BY world) AS fan_links
FROM members
)
SELECT world, brands, fan_links, rk, brand_name, fans
FROM ranked
WHERE rk <= 5 AND brands >= 15
ORDER BY fan_links DESC, rk;
The query returns five leading brands for each of 38 groups with at least 15 members. Three selected groups illustrate different audience relationships:
The 101-brand group places power tools beside kitchen appliances, providing a candidate home-project collection to inspect.
The saved capture contains every returned group and the execution record. Leiden returned the same groups in all 4 runs under different group numbers.
The map below lays out the same similarity graph with ForceAtlas2, using each brand's five strongest links, and colors brands by their Leiden group. Select groups to see where they sit and which brands have the most fans.
WITH fans AS (
SELECT -(e.user_id + 1) AS src, CAST(i.brand_id AS BIGINT) AS dst
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
WHERE e.event_type = 'item_like' AND i.brand_id <> 0
GROUP BY e.user_id, i.brand_id
), top_brands AS (
SELECT dst AS brand_id, COUNT(*) AS fans FROM fans GROUP BY dst ORDER BY fans DESC, dst LIMIT 5000
), similar AS (
SELECT first, second, similarity
FROM cugraph_jaccard(
edges => (SELECT src, dst FROM fans),
vertex_pairs => (SELECT a.brand_id AS first, b.brand_id AS second
FROM top_brands a JOIN top_brands b ON a.brand_id < b.brand_id),
directed => false)
WHERE similarity >= 0.02
), nearest AS (
SELECT a, b, similarity,
ROW_NUMBER() OVER (PARTITION BY a ORDER BY similarity DESC, b) AS rk
FROM (SELECT first AS a, second AS b, similarity FROM similar
UNION ALL SELECT second AS a, first AS b, similarity FROM similar) d
), worlds AS (
SELECT vertex AS brand_id, community AS world
FROM cugraph_leiden(
edges => (SELECT first AS src, second AS dst, CAST(similarity AS DOUBLE) AS weight FROM similar),
directed => false, resolution => 3, seed => 42)
), layout AS (
SELECT vertex AS brand_id, x, y
FROM cugraph_force_atlas2(
edges => (SELECT a AS src, b AS dst, CAST(similarity AS DOUBLE) AS weight FROM nearest WHERE rk <= 5),
directed => false, max_iter => 2000, barnes_hut_optimize => true,
outbound_attraction_distribution => true, lin_log_mode => true,
scaling_ratio => 5, gravity => 5, seed => 42)
), brands AS (
SELECT CAST(brand_id AS BIGINT) AS brand_id, MIN(brand_name) AS brand_name
FROM merrec_items WHERE brand_id <> 0 GROUP BY brand_id
)
SELECT l.brand_id, b.brand_name, t.fans, w.world, l.x, l.y
FROM layout l
JOIN worlds w ON w.brand_id = l.brand_id
JOIN top_brands t ON t.brand_id = l.brand_id
JOIN brands b ON b.brand_id = l.brand_id
ORDER BY l.brand_id;
Hover or tap a dot for its brand, world and fans.
- Unlabelled worlds2,073
Which brands connect the shopping worlds?
Betweenness centrality counts how often a brand lies on the shortest paths between other brands in the similarity graph. Brands that connect many worlds are candidates for cross-world navigation, or for exclusion from a collection that should stay inside one world.
The SQL builds the same Jaccard graph as the worlds query: 5,000
brands, edges where the shared-fan similarity reaches 0.02. Leiden assigns
the worlds and cugraph_betweenness_centrality scores the same edges. A
join of each brand's graph neighbors to their world labels counts how many
worlds the brand touches directly.
), worlds AS (
SELECT vertex AS brand_id, community AS world
FROM cugraph_leiden(
edges => (SELECT first AS src, second AS dst, CAST(similarity AS DOUBLE) AS weight FROM similar),
directed => false, resolution => 3, seed => 42)
), bridges AS (
SELECT vertex AS brand_id, value AS betweenness
FROM cugraph_betweenness_centrality(
edges => (SELECT first AS src, second AS dst FROM similar),
directed => false, normalized => true)
), neighbors AS (
SELECT first AS brand_id, second AS neighbor FROM similar
UNION ALL SELECT second, first FROM similar
), reach AS (
SELECT n.brand_id, COUNT(*) AS neighbors, COUNT(DISTINCT w.world) AS worlds_reached
FROM neighbors n JOIN worlds w ON w.brand_id = n.neighbor
GROUP BY n.brand_id
The query took 6.81 s over Flight SQL.
The connectors are mostly sellers and generic labels that the catalog stores in its brand field. Department stores and retailers (Walmart, JCPenney, Macy's, Claire's), house brands (Mainstays, Better Homes & Gardens) and catch-all entries (As Seen on TV, Customized & Personalized, Eyeshadow, US Vintage) hold most of the first fifteen positions, each with neighbors in 37 to 44 of the worlds. Samsung is the manufacturer with the highest score. Its 98 neighbors span 13 worlds. To isolate manufacturer brands from retail aggregators, exclude retailer and catch-all labels in the initial CTE and rerun the centrality query.
Betweenness depends on the graph the query defines. A different similarity threshold changes which paths exist, and a brand with few neighbors can score highly when it is the only link between two worlds. The world counts use the Leiden labels of the same execution.
The plan had 16 native fragments and zero host DataFusion boundaries. The largest query allocator peak was 54.79 GiB of the 90 GiB query limit. The capture contains the executed SQL, query IDs, timings and every result row.
The exclusion list came from a decision model. 12 of the first 20 entries in the published ranking are retailers, house brands or catch-all labels, and betweenness is a property of the paths through them, so deleting them from the output changes nothing for the other brands. The graph has to be rebuilt without them.
A context query joined each of the 120 ranked entries to its catalog
footprint: listings, distinct first and second level categories, the three
largest category paths, mean price and four sample titles. A hosted decision
model answered three questions per entry from that context alone, with no
graph numbers in the prompt: the kind of entry (standalone brand,
multi-brand retailer, superstore house brand, generic catch-all), the
probability that the entry is a standalone brand, and a 0 to 3 pollution
score. The application's policy accepts an entry when the probability is at
least 0.70 and the kind is standalone_brand,
rejects it below 0.30, and sends the rest to
review. The answers for all 120 entries became a Parquet file, registered as
brand_ontology, and the bridges query ran again with one change in its
first CTE: entries the policy rejected or held for review leave the fan
graph.
WITH fans AS (
SELECT -(e.user_id + 1) AS src, CAST(i.brand_id AS BIGINT) AS dst
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
LEFT JOIN brand_ontology o ON o.brand_id = CAST(i.brand_id AS BIGINT)
WHERE e.event_type = 'item_like' AND i.brand_id <> 0
AND (o.brand_id IS NULL OR o.band = 'accept')
AND e.event_time >= TIMESTAMP '2023-05-01' AND e.event_time < TIMESTAMP '2023-11-01'
GROUP BY e.user_id, i.brand_id
Pass 1 judged the published top 120: 63 accepted, 30 for review, 27 rejected (0 calls, $0.0054, 5.4 s). Review entries stay out of the graph too. The purified ranking placed 59 entries the first pass had not seen in its top 120, so a second pass judged those (0 calls) and recomputed once more, which brought 26 further unjudged entries into the top 120. The table lists the purified top 15 with each brand's rank in the published ranking and in the published ranking with the excluded entries deleted after the fact, its purified betweenness and the ratio of that score to its original one.
Samsung's betweenness doubles in the purified graph. Conair and Black & Decker move from the sixties
into the top 10, and Bella enters a top 15 it was absent from. Post-filtering
keeps 11 of these 15 brands but in a different order and cannot place Bella.
The full 202-row table with every brand that appears in any of the three
rankings is windows.all.ranking in the data file; the 43-entry
review queue of both passes, with its context, is windows.all.review_queue. Claire's is there with
a standalone probability of 0.89 and a kind of multi_brand_retailer at
0.98, the kind of disagreement the review band holds.
Two controls. A hand-written denylist of 49 retailer and catch-all names, run through the same recompute, excludes 45 of the same entries; the model also excludes 12 the list missed and keeps 4 the list names. The two purified rankings share 13 of their top 15. On the September to October window the model's answers are reused from the first run: 186 of 240 lookups hit the registry, the remaining 54 calls cost $0.0024, and the model excludes 18 entries the denylist does not know, among them Amazon, Sephora and QVC, while 7 of the list's names do not appear in that window's top 120 at all.
The probe and each recompute ran as one statement with 15 native fragments and zero host DataFusion boundaries; the probe took 8.76 s and the purified recompute 8.80 s on the May to October window, 4.13 s on September to October. The largest query allocator peak was 54.46 GiB of the 90 GiB query limit. Both passes together made 179 model calls for $0.0080. The data file holds the question set, the policy, the denylist, the executed SQL, the stage metrics, every judgment and every ranking and review row for both windows. The model's answers to the same entry vary slightly between calls, so a rerun that ignores the registry is a new sample.
Does a Pokemon lookalike audience find new interest?
The lookalike list contains shoppers whose taste vectors resemble earlier Pokemon buyers. The query checks whether they later bought Pokemon and whether they had already viewed it, against the simple rule of selecting previous Pokemon viewers.
Earlier CTEs construct 64 category-preference features from views before
September 1, 2023. Each value is the square root of a shopper's share of views
in one of the 64 busiest categories. Eligibility requires 20 views. The seed
contains 1,302 eligible July to August Pokemon buyers; candidates excludes them.
The unfolded part of the SQL searches for 20 neighbors per seed buyer, deduplicates the list, splits it by prior Pokemon views and joins September to October purchases. The folded relations build the features in the same query.
), lookalikes AS (
SELECT DISTINCT neighbor_id AS id
FROM cuvs_brute_force_knn(
dataset => (SELECT * FROM candidates ORDER BY id),
queries => (SELECT t.* FROM taste t JOIN seed s ON s.id = t.id ORDER BY t.id),
k => 20, metric => 'l2_expanded')
), pokemon_viewers AS (
SELECT DISTINCT user_id AS id FROM views WHERE brand_id = 17470
), later_buyers AS (
SELECT DISTINCT user_id AS id FROM pokemon_buys WHERE later = 1
), audiences AS (
SELECT labels.audience, l.id
FROM lookalikes l LEFT JOIN pokemon_viewers v ON v.id = l.id
CROSS JOIN (
SELECT 'lookalikes' AS audience, 0 AS prior_view
UNION ALL SELECT 'lookalikes with prior Pokemon views', 1
UNION ALL SELECT 'lookalikes without prior Pokemon views', 2
) labels
WHERE labels.prior_view = 0
OR (labels.prior_view = 1 AND v.id IS NOT NULL)
OR (labels.prior_view = 2 AND v.id IS NULL)
UNION ALL SELECT 'pokemon viewers', c.id FROM candidates c JOIN pokemon_viewers v ON v.id = c.id
UNION ALL SELECT 'all active shoppers', c.id FROM candidates c
)
SELECT a.audience, COUNT(*) AS shoppers, COUNT(b.id) AS buyers_sep_oct,
CAST(COUNT(b.id) AS DOUBLE) / COUNT(*) AS purchase_rate
FROM audiences a LEFT JOIN later_buyers b ON b.id = a.id
GROUP BY a.audience
ORDER BY purchase_rate DESC;
The two prior-view rows partition the lookalike audience. They are not additional shoppers beyond the 19,125 in the first row.
Of the 132 later buyers in the lookalike list, 130 had already viewed Pokemon. The never-viewed group's 0.034% purchase rate is close to the 0.033% eligible baseline. This result supplies little evidence for finding a new audience.
The direct-viewer rule reaches 572 buyers from a much larger list. The campaign list compares the methods at equal list size. Features use the latest retained category attributes.
The capture records the SQL and results used here.
Can an existing model improve a fixed-size buyer shortlist?
The recent-purchase rule selects the 55,404 shoppers who bought in the previous 30 days. The model list keeps the same number of highest-scoring shoppers, and the query counts September buyers in each list.
The model was trained before the query on features preceding August 1, 2023,
with purchases in the next 30 days as labels. Training and model review are
separate preparation steps; SQL loads propensity_xgb.json for inference.
The earlier test CTE computes ten activity and price features from June to August,
retains shoppers with at least 20 views, and attaches the September outcome.
The visible query segment below scores those features, ranks shoppers, and evaluates both budget allocations.
scores AS (
SELECT id, prediction AS score FROM cuml_forest_predict(
input => (SELECT id,
LN(1 + views) AS views,
LN(1 + likes) AS likes,
LN(1 + intents) AS intents,
LN(1 + buys) AS buys,
LN(1 + gmv) AS gmv,
LN(1 + views_30d) AS views_30d,
LN(1 + likes_30d) AS likes_30d,
LN(1 + intents_30d) AS intents_30d,
LN(1 + buys_30d) AS buys_30d,
LN(1 + mean_price) AS mean_price FROM test ORDER BY id),
model => 'propensity_xgb.json', model_format => 'xgboost_json')
), ranked AS (
SELECT t.label, t.buys_30d,
ROW_NUMBER() OVER (ORDER BY s.score DESC, t.id) AS rk,
COUNT(*) OVER () AS shoppers,
SUM(CASE WHEN t.buys_30d > 0 THEN 1 ELSE 0 END) OVER () AS rule_size
FROM scores s JOIN test t ON t.id = s.id
)
SELECT MAX(shoppers) AS shoppers, MAX(shoppers) / 100 AS top_1pct, MAX(rule_size) AS rule_size,
SUM(label) AS buyers,
SUM(CASE WHEN rk <= shoppers / 100 THEN label ELSE 0 END) AS model_top_1pct_buyers,
SUM(CASE WHEN rk <= rule_size THEN label ELSE 0 END) AS model_rule_size_buyers,
SUM(CASE WHEN buys_30d > 0 THEN label ELSE 0 END) AS rule_buyers
FROM ranked;
The evaluation contains 2,017,739 eligible shoppers and 38,925 September buyers.
At equal list size, the model includes 1,980 more observed buyers than the recent-purchase rule. The smaller model list has higher precision but reaches fewer buyers.
Prices use the latest retained item attributes, which also applies to training.
The capture contains the full result and execution record.
Which catalog entries are one brand listed twice?
Sellers can enter the same brand under several names. Two entries liked by
the same people are candidates for one brand. The input is every item_like
event of the six months, reduced to distinct user-brand pairs.
Jaccard divides the shared fans of two entries by the union of their fan sets. The query scores all 1,999,000 pairs of the 2,000 most-liked brands in one call, then joins names and fan counts and keeps the 30 highest scores.
WITH fans AS (
SELECT -(e.user_id + 1) AS src, CAST(i.brand_id AS BIGINT) AS dst
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
WHERE e.event_type = 'item_like' AND i.brand_id <> 0
GROUP BY e.user_id, i.brand_id
), top_brands AS (
SELECT dst AS brand_id, COUNT(*) AS fans FROM fans GROUP BY dst ORDER BY fans DESC, dst LIMIT 2000
), brands AS (
SELECT CAST(brand_id AS BIGINT) AS brand_id, MIN(brand_name) AS brand_name
FROM merrec_items WHERE brand_id <> 0 GROUP BY brand_id
)
SELECT ba.brand_name AS brand_a, bb.brand_name AS brand_b,
a.fans AS fans_a, b.fans AS fans_b, r.similarity AS jaccard
FROM cugraph_jaccard(
edges => (SELECT src, dst FROM fans),
vertex_pairs => (SELECT a.brand_id AS first, b.brand_id AS second
FROM top_brands a JOIN top_brands b ON a.brand_id < b.brand_id),
directed => false
) r
JOIN top_brands a ON a.brand_id = r.first
JOIN top_brands b ON b.brand_id = r.second
JOIN brands ba ON ba.brand_id = r.first
JOIN brands bb ON bb.brand_id = r.second
ORDER BY r.similarity DESC, brand_a
LIMIT 30;
The table shows the first 14 of 30 rows. The query took 3.34 s over Flight SQL.
Most of the highest-scoring pairs are one brand entered twice: TXT and Tomorrow X Together, Air Jordan and Jordan, Mugler and Thierry Mugler, Yves Saint Laurent and its YSL variant, Christian Dior and Dior, A Bathing Ape and Bape, Hermès and HERMES, BabyBjorn and BABYBJÖRN. No name was compared to find them. The remaining pairs are distinct brands that one audience follows together, such as Hello Kitty and Sanrio, Konami and Yu-Gi-Oh!, or Panini and Topps.
The pair list is a SQL relation, so the same query could join each pair's leaf categories or price ranges to separate duplicates from companions. Scores over the full six months hide seasonal pairs; a date filter on the events restricts the window.
The plan had 6 native fragments and zero host DataFusion boundaries. The largest query allocator peak was 75.07 GiB of the 90 GiB query limit. The capture contains the executed SQL, query IDs, timings and every result row.
Which of those pairs are one brand? A decision model answered for the
120 highest-scoring pairs. The pair list is the query above with the fan
graph restricted to entries the bridges follow-up accepted as
standalone brands, so retailers and catch-all labels pair with nothing, and
the limit raised from 30 to 120. For each pair the model saw the Jaccard
score and both entries' listings, mean price, two largest category paths and
three sample titles, and answered three questions: the relationship
(same_entity_alias, sub_brand_or_diffusion_line,
ip_franchise_and_owner_maker, distinct_co_liked_peers), the probability
that the two ids should merge, and a 0 to 3 alignment score. The policy
accepts a merge at a probability of 0.70 or more
when the relationship is same_entity_alias, rejects it below
0.30, and sends the rest to review.
18 pairs were accepted, 7 sent to review and 95 rejected (120 calls, $0.0062, 5.6 s). The rejects include the pairs a fan-overlap rule alone would merge: Rilakkuma and San-X, Hello Kitty and Sanrio, Konami and Yu-Gi-Oh!, Panini and Topps. The review queue holds Hourglass Cosmetics and Hourglass at 0.69, Beats by Dr. Dre and Beats at 0.68, and the sub-brand pairs Air Jordan and Jordan, Polo Ralph Lauren and Ralph Lauren.
Accepted pairs are edges. cugraph_weakly_connected_components gives each
entry the smallest brand id of its component as a canonical id, and a scan
of every like event counts, per canonical brand, the exact number of
distinct users who liked any member. Every merged brand has two ids; the
table compares the fans of the id with more of them against the distinct
count over both, and the share of the summed count that is the same people
twice.
SELECT vertex AS brand_id, component AS canonical_id
FROM cugraph_weakly_connected_components(
edges => (SELECT brand_id_a AS src, brand_id_b AS dst FROM brand_pairs WHERE band = 'accept'),
directed => false
)
ORDER BY canonical_id, brand_id
WITH likes AS (
SELECT c.canonical_id, c.brand_id, e.user_id
FROM merrec_events e
JOIN merrec_items i ON i.item_id = e.item_id
JOIN brand_canonical c ON c.brand_id = CAST(i.brand_id AS BIGINT)
WHERE e.event_type = 'item_like'
), member_fans AS (
SELECT canonical_id, brand_id, COUNT(DISTINCT user_id) AS fans
FROM likes GROUP BY canonical_id, brand_id
), union_fans AS (
SELECT canonical_id, COUNT(DISTINCT user_id) AS exact_union_fans
FROM likes GROUP BY canonical_id
These are exact counts over all 182.9 million like events. Whichever id a report used for Dior, it was missing about a third of the brand's fans, and adding the two ids would have counted a quarter of the sum twice.
A string control proposes pairs by word-trigram similarity over all 20,000 names, with a threshold of 0.5 (pg_trgm's default 0.3 pairs Dior with Christian Dior and a great deal else). It finds 9 of the 18 pairs the policy merged and misses the acronyms, rebrands and spellings: TXT, Bape, Dior, LOFT, HERMES, Thierry Mugler, L'Oreal, Thrive, e.l.f. Cosmetics. Its 7,310 proposals over the whole catalog are mostly case and punctuation variants, with pairs that share every trigram such as Boom Boom and Boom. Judging every one of the 199,990,000 pairs instead of the probe's 120 would cost about $10,277 at this run's mean cost per decision; the probe keeps the question affordable.
The probe ran with 6 native fragments and zero host DataFusion boundaries in 3.29 s; the component labels took 0.02 s and the fan count over all likes 2.85 s. The largest query allocator peak was 69.88 GiB of the 90 GiB query limit. The trigram control ran on the CPU in 2.1 s. The data file holds the question set, the policy, the executed SQL, the stage metrics, all 120 judged pairs, the review queue, the merged clusters and the control's found, missed and wrongly proposed pairs.
Is session_id a per-user session key?
The events table has a session_id column, and grouping by it looks like
the obvious way to build sessions. A graph checks that assumption before
any session analysis depends on it. A standard GROUP BY session_id HAVING COUNT(DISTINCT user_id) > 1
identifies whether session IDs collide, but does not calculate transitive collisions across multiple keys and users.
Constructing a bipartite graph between users and session IDs and computing Weakly Connected Components (WCC) uncovers transitive chains. If User A shares Session 1 with User B, and User B shares Session 2 with User C, WCC assigns all three to one component. If every session ID belonged to an isolated user, every connected component would contain exactly one user.
Graph vertices can be strings, so a user vertex is user: followed by the
user ID and a session vertex is the session ID itself. The two kinds cannot
collide. The query groups the components by how many users they contain.
WITH october AS (
SELECT DISTINCT user_id, session_id
FROM merrec_events
WHERE event_time >= TIMESTAMP '2023-10-01 00:00:00'
), components AS (
SELECT vertex, component FROM cugraph_weakly_connected_components(
edges => (SELECT CONCAT('user:', CAST(user_id AS VARCHAR)) AS src, session_id AS dst FROM october),
directed => false)
), user_components AS (
SELECT component, COUNT(*) AS users FROM components WHERE STARTS_WITH(vertex, 'user:') GROUP BY component
)
SELECT CASE WHEN users = 1 THEN '1 user'
WHEN users <= 10 THEN '2-10 users'
WHEN users <= 1000 THEN '11-1,000 users'
ELSE 'over 1,000 users' END AS component_size,
COUNT(*) AS components, SUM(users) AS users, MAX(users) AS largest
FROM user_components
GROUP BY 1
ORDER BY largest;
The query took 1.58 s over Flight SQL. An equivalent CPU pipeline combining 32-thread DuckDB with scipy's connected components took 12.3 s on the same machine (9.3 s in string deduplication and renumbering, 3.0 s in WCC) and produced identical components.
One component links 2,649,977 of
October's 2,986,296 users through shared session IDs. Only
335,591 users have session
IDs that nobody else uses. Across the six months, 15,138,756 of the
25,864,474 distinct session IDs appear for more than one user, and one ID
appears for 54 users. Session analysis on this dataset has to group by
(user_id, session_id).
The same query, with the column substituted, audits any other identifier such as a device, cart or order ID. A component with more than one owner is the case to investigate.
The plan had 2 native fragments and zero host DataFusion boundaries. The largest query allocator peak was 33.83 GiB of the 90 GiB query limit. The capture contains the executed SQL, query IDs, timings and every result row.
Data scope
The source contains 1,268,092,738 events, 82,614,392 items and 5,566,055 users.
merrec_events retains events; merrec_items keeps the latest attributes for
each item. mr_taste64, read by the campaign and lookalike queries, is a
prepared table of 64 category-share features per shopper, built by one SQL
statement over the same events. Its SQL is on the
approximate search page.
A fan is a distinct user with an item_like event for a brand. A purchase is a
buy_comp event. Repeated views count toward behavioral features. Each query
states its time window and eligible audience. Quoted timings include planning,
execution and result receipt. Item names, categories and prices are the latest
retained values, not attributes reconstructed at event time.
Each section links to its saved SQL and results, with server configurations and execution metrics. Each query ran four times, and the quoted timing is the median of runs 2 to 4 with the source pages resident in the OS page cache. Detailed reproduction steps and scripts are documented in the scale README. Each query uses one GPU and its working set must fit the configured memory limit. The largest allocator peak among these queries was 75.1 GiB of a 90 GiB grant, in the duplicate-brand query.
MerRec is published by Mercari, Inc. under
CC BY-NC 4.0. The saved
results under /data/merrec-*.json are derived from it and carry the same
terms, recorded in LICENSE-MERREC.md; they are not
covered by the repository's Apache-2.0 license.