FREE AI-Enhanced Online PostgreSQL Compiler

Untitled*

You must execute the code again to save those values in the database.

-- ============================================================
-- 1) Create base table to simulate incoming orders
-- ============================================================
CREATE TABLE orders (
    order_id INT,
    customer_id VARCHAR(10),
    order_date DATE,
    order_amount DECIMAL(10,2),
    ingested_at TIMESTAMP
);

-- ============================================================
-- 2) Insert sample data (intentionally includes duplicates)
--    Same order_id appears multiple times with different
--    ingested_at timestamps
-- ============================================================
INSERT INTO orders VALUES
(101, 'C001', '2024-01-01', 500.00, '2024-01-01 10:00:00'),
(101, 'C001', '2024-01-01', 500.00, '2024-01-01 10:05:00'),
(102, 'C002', '2024-01-02', 300.00, '2024-01-02 11:00:00'),
(103, 'C003', '2024-01-03', 450.00, '2024-01-03 12:00:00'),
(103, 'C003', '2024-01-03', 450.00, '2024-01-03 12:10:00');

SELECT *
FROM orders
ORDER BY order_id, ingested_at;

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY order_id
               ORDER BY ingested_at DESC
           ) AS rn
    FROM orders
) t
WHERE rn = 1;

DELETE FROM orders
WHERE ctid IN (
    SELECT ctid
    FROM (
        SELECT ctid,
               ROW_NUMBER() OVER (
                   PARTITION BY order_id
                   ORDER BY ingested_at DESC
               ) AS rn
        FROM orders
    ) t
    WHERE rn > 1
);

SELECT *
FROM orders
ORDER BY order_id, ingested_at;

INSERT INTO orders VALUES
(101, 'C001', '2024-01-01', 500.00, '2024-01-01 10:00:00'),
(103, 'C003', '2024-01-03', 450.00, '2024-01-03 12:00:00');

SELECT *
FROM orders
ORDER BY order_id, ingested_at;

SELECT *
FROM orders c1
WHERE ingested_at = (
    SELECT MAX(c2.ingested_at)
    FROM orders c2
    WHERE c1.order_id = c2.order_id
);

CREATE TABLE orders_dedup AS
SELECT t.order_id, t.customer_id, t.order_date, t.order_amount
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id, order_date, order_amount
           ) AS rn
    FROM orders
) t
WHERE rn = 1;

SELECT *
FROM orders_dedup
ORDER BY order_id, ingested_at;

order_id	customer_id	order_date	order_amount	ingested_at
101	C001	2024-01-01	500.00	2024-01-01 10:00:00
101	C001	2024-01-01	500.00	2024-01-01 10:05:00
102	C002	2024-01-02	300.00	2024-01-02 11:00:00
103	C003	2024-01-03	450.00	2024-01-03 12:00:00
103	C003	2024-01-03	450.00	2024-01-03 12:10:00

order_id	customer_id	order_date	order_amount	ingested_at	rn
101	C001	2024-01-01	500.00	2024-01-01 10:05:00	1
102	C002	2024-01-02	300.00	2024-01-02 11:00:00	1
103	C003	2024-01-03	450.00	2024-01-03 12:10:00	1

order_id	customer_id	order_date	order_amount	ingested_at
101	C001	2024-01-01	500.00	2024-01-01 10:05:00
102	C002	2024-01-02	300.00	2024-01-02 11:00:00
103	C003	2024-01-03	450.00	2024-01-03 12:10:00

order_id	customer_id	order_date	order_amount	ingested_at
101	C001	2024-01-01	500.00	2024-01-01 10:00:00
101	C001	2024-01-01	500.00	2024-01-01 10:05:00
102	C002	2024-01-02	300.00	2024-01-02 11:00:00
103	C003	2024-01-03	450.00	2024-01-03 12:00:00
103	C003	2024-01-03	450.00	2024-01-03 12:10:00

order_id	customer_id	order_date	order_amount	ingested_at
101	C001	2024-01-01	500.00	2024-01-01 10:05:00
102	C002	2024-01-02	300.00	2024-01-02 11:00:00
103	C003	2024-01-03	450.00	2024-01-03 12:10:00

CREATE TABLE
INSERT 0 5

DELETE 2

INSERT 0 2

SELECT 3