logins(user_id, device_id, login_at) and purchases(order_id, user_id, merchant_id, amount, ordered_at)

For September 2026, find merchants that look like the centre of a ring. A purchasing user counts as suspicious for a merchant when they share a device with another of that merchant's purchasing users, where "share" means both of them logged into the same device in the 90 days up to one of their own purchases at that merchant.

Return merchant_id and suspicious_users, the count of distinct users caught in at least one such pair, for merchants with three or more of them, ordered by merchant_id.

Two users logging into the same device is not enough on its own: the logins have to sit inside each user's own 90-day pre-purchase window, which is what makes this a join on time as well as on device.

Tables

logins

user_id  device_id  login_at
-------  ---------  -------------------
1        D1         2026-08-20 10:00:00
2        D1         2026-08-21 10:00:00
3        D1         2026-08-22 10:00:00
4        D1         2025-09-01 10:00:00
5        D2         2026-08-25 10:00:00
6        D2         2026-08-26 10:00:00
7        D3         2026-08-27 10:00:00
8        D4         2026-08-28 10:00:00
... 1 more row(s)

purchases

order_id  user_id  merchant_id  amount  ordered_at
--------  -------  -----------  ------  -------------------
1         1        100          10.00   2026-09-05 10:00:00
2         2        100          10.00   2026-09-06 10:00:00
3         3        100          10.00   2026-09-07 10:00:00
4         4        101          10.00   2026-09-08 10:00:00
5         5        101          10.00   2026-09-09 10:00:00
6         6        101          10.00   2026-09-10 10:00:00
7         7        102          10.00   2026-09-11 10:00:00
8         8        102          10.00   2026-09-12 10:00:00
... 1 more row(s)

Expected result

merchant_id  suspicious_users
-----------  ----------------
100          3