Find popular pairs from table with transcations

user_18202887 0
Viewed 60

I have an already simplified table of transactions with the customers and the unique items they purchased. Example:

| Customer email   | Item             |
| ---------------- | ---------------- |
| First            | row              |
| a@hotmail.com    | 111              |
| a@hotmail.com    | 112              |
| a@hotmail.com    | 113              |
| b@gmail.com      | 111              |
| b@gmail.com      | 112              |
| c@aol.com        | 110              |
| c@aol.com        | 111              | 
| c@aol.com        | 113              |

I want to get a list of popular pairs (combinations) within one client with a number of occurrences:

| item1   | item2 | number of occurrences|              
| --------| ----- | -------------------- |
| '111'   | '112' | 2                    |
| '111'   | '113' | 2                    |
| '112'   | '113' | 1                    |
| '110'   | '111' | 1                    |
| '110'   | '113' | 1                    |

Is it possible to achieve using SQL? Or I should use something else.

Many thanks for your help in advance.

google-bigquery sql
3 Answers

Consider below query:

WITH purchased AS (
  SELECT customer_email, ARRAY_AGG(DISTINCT item ORDER BY item) items
    FROM sample GROUP BY 1
),
associations AS (
  SELECT customer_email, STRUCT(first, second) AS item_pair
    FROM purchased,
         UNNEST(items) first WITH OFFSET o1
    JOIN UNNEST(items) second WITH OFFSET o2 ON o1 < o2
)
SELECT FORMAT('%t', item_pair) item_pair, COUNT(1) cnt
  FROM associations
 GROUP BY 1 ORDER BY 2 DESC;

output will be:

enter image description here

with sample:

CREATE TEMP TABLE sample AS
SELECT 'a@hotmail.com' customer_email, '111' item UNION ALL
SELECT 'a@hotmail.com', '112' UNION ALL
SELECT 'a@hotmail.com', '113' UNION ALL
SELECT 'b@gmail.com', '111' UNION ALL
SELECT 'b@gmail.com', '112' UNION ALL
SELECT 'c@aol.com', '110' UNION ALL
SELECT 'c@aol.com', '111' UNION ALL
SELECT 'c@aol.com', '113';
user_190399200

Consider below approach

select item as item1, item2, count(*) as occurrences from (
  select *, array_agg(item) over win as other_items
  from your_table
  window win as (partition by email order by item rows between 1 following and unbounded following)
) t, t.other_items as item2
group by item1, item2

if applied to sample data in y our question - output is

enter image description here

user_52219440 
select
    p1.item as item1,
    p2.item as item2,
    count(*) as occurrences
from
    purchases p1
    join
    purchases p2 on p2.customer_email = p1.customer_email and p2.item > p1.item
group by
    p1.item,
    p2.item
order by
    occurrences desc
user_186489000
Related