By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.19 |
CREATE TABLE purchasing (
event_type VARCHAR(255),
product VARCHAR(255),
quantity VARCHAR(255)
);
INSERT INTO purchasing
(event_type, product, quantity)
VALUES
("Offer", "Product_A", "300"),
("Offer", "Product_B", "200"),
("Offer", "Product_C", "500"),
("Offer", "Product_D", "400"),
("Offer", "Product_E", "600"),
("Order", "Product_B", "250"),
("Order", "Product_C", "450");
select
product,
event_type,
quantity
from
(
select
p.*,
max(event_type) over (partition by product) as best_event_type
from purchasing p
) ranked
where event_type = best_event_type;
| product | event_type | quantity |
|---|---|---|
| Product_A | Offer | 300 |
| Product_B | Order | 250 |
| Product_C | Order | 450 |
| Product_D | Offer | 400 |
| Product_E | Offer | 600 |