/*
Google SQL Interview Question

You are given a transaction table, which records transactions between sellers and buyers.
The structure of the table is as follows: 

| Column         | Data Type | Description                   |
|----------------|-----------|-------------------------------|
| Transaction_ID | INT       | Unique transaction identifier |
| Customer_ID    | INT       | Unique customer identifier    |
| Amount         | INT       | Transaction amount            |
| Date           | TIMESTAMP | Transaction date and time     |

Every successful transaction will have two row entries into the table with two different transaction_id but in ascending order sequence,
the first one for the seller where their customer_id will be registered, and the second one for the buyer where their customer_id will be registered.
The amount and date_time for both will however be the same. 

Write an sql query to find the 5 top seller-buyer combinations who have had maximum transactions between them. 

Condition - Please disqualify the sellers who have acted as buyers and also the buyers who have acted as sellers for this condition.
*/

CREATE TABLE Transactions (
    transaction_id INT PRIMARY KEY,
    customer_id INT,
    amount INT,
    tran_Date DATETIME
);

INSERT INTO Transactions (transaction_id, customer_id, amount, tran_Date) VALUES
(1, 101, 500, '2025-01-01 10:00:01'),
(2, 201, 500, '2025-01-01 10:00:01'),
(3, 102, 300, '2025-01-02 00:50:01'),
(4, 202, 300, '2025-01-02 00:50:01'),
(5, 101, 700, '2025-01-03 06:00:01'),
(6, 202, 700, '2025-01-03 06:00:01'),
(7, 103, 200, '2025-01-04 03:00:01'),
(8, 203, 200, '2025-01-04 03:00:01'),
(9, 101, 400, '2025-01-05 00:10:01'),
(10, 201, 400, '2025-01-05 00:10:01'),
(11, 101, 500, '2025-01-07 10:10:01'),
(12, 201, 500, '2025-01-07 10:10:01'),
(13, 102, 200, '2025-01-03 10:50:01'),
(14, 202, 200, '2025-01-03 10:50:01'),
(15, 103, 500, '2025-01-01 11:00:01'),
(16, 101, 500, '2025-01-01 11:00:01'),
(17, 203, 200, '2025-11-01 11:00:01'),
(18, 201, 200, '2025-11-01 11:00:01');


WITH data AS
(
  SELECT
    transaction_id,
    customer_id AS seller_id,
    amount,
    tran_Date,
    LEAD(customer_id,1) OVER(ORDER BY transaction_id) AS buyer_id
  FROM Transactions
)
,combinations AS
(SELECT
   seller_id,
   buyer_id,
   COUNT(1) AS no_of_transactions
 FROM data
 WHERE transaction_id%2 = 1
 GROUP BY 1,2)
,disqualified_customers AS
(
 SELECT * FROM 
 (SELECT
   seller_id
  FROM combinations) sellers
 INNER JOIN
 (SELECT
   buyer_id
  FROM combinations) buyers
 ON buyers.buyer_id = sellers.seller_id
)
SELECT *
FROM combinations
WHERE
 seller_id NOT IN (SELECT seller_id FROM disqualified_customers)
 AND buyer_id NOT IN (SELECT buyer_id FROM disqualified_customers);