dbdbexercises
Practice Free
← Syllabus|
Hard

16. Frequently Co-Purchased Product Pairs (Self-Join)

HardJoinsPostgreSQL 16PRO
Table: order_items
+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id    | int  |
| product_id  | int  |
+-------------+------+

order_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Market basket analysis uses self-joins on shopping cart items to uncover item pairs that customers consistently purchase together in the same order.

Self-join `order_items` (`p1` and `p2`) on `order_id` where `p1.product_id < p2.product_id` (to avoid duplicates and self-pairings). Group by the pair of products and count how many distinct orders contained both products. Return: 1. `p1.product_id` AS `product_a` 2. `p2.product_id` AS `product_b` 3. `pair_order_count`: Number of shared orders Filter for pairs with `pair_order_count >= 2`. Order results by `pair_order_count` descending, then by `product_a` ascending, then by `product_b` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Co-purchased product pairs appearing in at least 2 common orders.

Constraints:
  • Query must utilize clause(s): JOINGROUP BYHAVING
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Frequently Co-Purchased Product Pairs (Self-Join)” SQL Problem

Business Scenario

Market basket analysis uses self-joins on shopping cart items to uncover item pairs that customers consistently purchase together in the same order.

Expected Output & Criteria

Co-purchased product pairs appearing in at least 2 common orders.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE order_items (
        order_id INT NOT NULL,
        product_id INT NOT NULL,
        PRIMARY KEY (order_id, product_id)
      );

Target SQL Clauses & Operators

JOINGROUP BYHAVING

Want more Joins practice?

Explore the full syllabus of 16 exercises and syntax guides.

View Joins Track