Last active
September 19, 2025 16:24
-
-
Save jhumigas/2288b4f89355d0a36f262ebc4551ef52 to your computer and use it in GitHub Desktop.
SQL Bids Puzzle
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Basically we have a simple app handling bids | |
| -- Each participant submits a single, sealed bid, meaning their bid is hidden from other bidders. | |
| -- We found a bug in where some users were able to bid against themselves | |
| -- The task: Find the duplicated bids in the following format (lot_id, buyer_id, duplicated_bids) | |
| -- Bids schema and seed data simulating a bug where a buyer can bid | |
| -- consecutively on the same lot (i.e., bidding against themselves) | |
| -- Here are some queries to simulate that on PostgreSQL | |
| DROP TABLE IF EXISTS bids; | |
| CREATE TABLE IF NOT EXISTS bids ( | |
| bid_id SERIAL PRIMARY KEY, | |
| lot_id INTEGER NOT NULL, | |
| buyer_id INTEGER NOT NULL, | |
| dt TIMESTAMP NOT NULL | |
| ); | |
| -- Seed data: multiple lots with some consecutive self-bids | |
| DELETE FROM bids; | |
| -- Lot 1: buyer 101 self-bids twice, buyer 102 self-bids twice | |
| INSERT INTO bids (lot_id, buyer_id, dt) VALUES | |
| (1, 101, '2024-01-01 10:00:00'), | |
| (1, 101, '2024-01-01 10:01:00'), -- self-bid | |
| (1, 102, '2024-01-01 10:02:00'), | |
| (1, 102, '2024-01-01 10:03:00'), -- self-bid | |
| (1, 101, '2024-01-01 10:04:00'); | |
| -- Lot 2: buyer 10 then buyer 104 self-bids then 105, then 106 self-bids 2 times | |
| INSERT INTO bids (lot_id, buyer_id, dt) VALUES | |
| (2, 103, '2024-01-02 09:00:00'), | |
| (2, 104, '2024-01-02 09:01:00'), | |
| (2, 104, '2024-01-02 09:02:00'), -- self-bid | |
| (2, 105, '2024-01-02 09:03:00'), | |
| (2, 106, '2024-01-02 09:04:00'), | |
| (2, 106, '2024-01-02 09:05:00'), -- self bid | |
| (2, 106, '2024-01-02 09:06:00'); -- self bid | |
| -- Lot 3: no consecutive self-bids | |
| INSERT INTO bids (lot_id, buyer_id, dt) VALUES | |
| (3, 201, '2024-01-03 08:00:00'), | |
| (3, 202, '2024-01-03 08:01:00'), | |
| (3, 201, '2024-01-03 08:02:00'); | |
| -- Expected duplicated bids | |
| --- Result of duplicated bids | |
| -- lot_id, buyer_id, duplicated_bid_ids | |
| -- 1,101, [2] | |
| -- 1,102, [4] | |
| -- 2,104, [8] | |
| -- 2,106, [11,12] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment