데이터베이스 안티패턴과 디자인 패턴 — 쇼핑몰 예제로 정리하기

-- 안티패턴
CREATE TABLE product (
  product_id   BIGINT PRIMARY KEY,
  name         VARCHAR(200),
  category_ids VARCHAR(100)   -- '12,34,56'
);
 
-- 12번 카테고리의 상품 찾기: 인덱스를 쓸 수 없고, 패턴 매칭이 깨지기 쉬움
SELECT * FROM product WHERE FIND_IN_SET('12', category_ids) > 0;   -- MySQL

CREATE TABLE product_category (
  product_id  BIGINT REFERENCES product(product_id),
  category_id BIGINT REFERENCES category(category_id),
  PRIMARY KEY (product_id, category_id)
);
CREATE INDEX ix_pc_category ON product_category(category_id);  -- 카테고리 → 상품 방향 탐색용

CREATE TABLE product (
  product_id BIGINT PRIMARY KEY,
  name       VARCHAR(200),
  tag1       VARCHAR(20),
  tag2       VARCHAR(20),
  tag3       VARCHAR(20)
);
 
SELECT * FROM product WHERE '무료배송' IN (tag1, tag2, tag3);
-- 태그가 4개가 필요해지는 순간 스키마 변경 + 모든 쿼리 수정

CREATE TABLE product_tag (
  product_id BIGINT REFERENCES product(product_id),
  tag        VARCHAR(20),
  PRIMARY KEY (product_id, tag)
);

CREATE TABLE category (
  category_id BIGINT PRIMARY KEY,
  parent_id   BIGINT REFERENCES category(category_id),
  name        VARCHAR(100)
);

-- PostgreSQL / MySQL 8.0+ (SQL Server는 RECURSIVE 키워드 없이 WITH)
WITH RECURSIVE tree AS (
  SELECT category_id, parent_id, name, 1 AS depth
  FROM category WHERE category_id = :root
  UNION ALL
  SELECT c.category_id, c.parent_id, c.name, t.depth + 1
  FROM category c JOIN tree t ON c.parent_id = t.category_id
)
SELECT * FROM tree;
 
-- Oracle: 전통적인 CONNECT BY (11gR2+는 재귀 WITH도 지원)
SELECT category_id, name, LEVEL
FROM category
START WITH category_id = :root
CONNECT BY PRIOR category_id = parent_id;

-- 안티패턴: 교차 테이블에 불필요한 id
CREATE TABLE product_category (
  id          BIGINT PRIMARY KEY AUTO_INCREMENT,
  product_id  BIGINT,
  category_id BIGINT
  -- UNIQUE (product_id, category_id)를 빠뜨리면 같은 매핑이 중복 저장됨
);

CREATE TABLE product_attr (
  product_id BIGINT,
  attr_name  VARCHAR(50),    -- 'color', 'size', 'ram_gb', 'screen_inch'
  attr_value VARCHAR(255),   -- 모든 값이 문자열
  PRIMARY KEY (product_id, attr_name)
);

SELECT product_id FROM product_attr
WHERE attr_name = 'screen_inch' AND attr_value > 50;

-- PostgreSQL: JSONB + GIN
CREATE TABLE product (
  product_id   BIGINT PRIMARY KEY,
  product_type VARCHAR(30) NOT NULL,
  name         VARCHAR(200) NOT NULL,
  spec         JSONB NOT NULL DEFAULT '{}'
);
CREATE INDEX ix_product_spec ON product USING GIN (spec jsonb_path_ops);
 
-- 포함 검색: {"color": "black"}인 상품
SELECT * FROM product WHERE spec @> '{"color": "black"}';
 
-- 자주 범위 조회하는 키는 표현식 인덱스로 승격
CREATE INDEX ix_product_screen ON product (((spec->>'screen_inch')::numeric));
 
-- MySQL 8.0: 생성 컬럼으로 뽑아서 인덱싱
ALTER TABLE product
  ADD COLUMN screen_inch DECIMAL(5,1)
    GENERATED ALWAYS AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(spec, '$.screen_inch')) AS DECIMAL(5,1))) STORED,
  ADD INDEX ix_screen_inch (screen_inch);

CREATE TABLE comment (
  comment_id  BIGINT PRIMARY KEY,
  target_type VARCHAR(20),   -- 'REVIEW' 또는 'ARTICLE'
  target_id   BIGINT,        -- review.review_id 또는 article.article_id
  body        TEXT
);

-- 댓글과 원글 제목을 함께 조회하려면
SELECT c.*, COALESCE(r.title, a.title) AS target_title
FROM comment c
LEFT JOIN review  r ON c.target_type = 'REVIEW'  AND r.review_id  = c.target_id
LEFT JOIN article a ON c.target_type = 'ARTICLE' AND a.article_id = c.target_id;

-- (A) 대상별 교차 테이블: 참조 방향을 뒤집음
CREATE TABLE review_comment (
  review_id  BIGINT REFERENCES review(review_id),
  comment_id BIGINT REFERENCES comment(comment_id) UNIQUE,
  PRIMARY KEY (review_id, comment_id)
);
CREATE TABLE article_comment ( /* 동일 구조 */ );
 
-- (B) 공통 상위 테이블: review와 article이 content를 상속
CREATE TABLE content (content_id BIGINT PRIMARY KEY, created_at TIMESTAMP NOT NULL);
CREATE TABLE review  (content_id BIGINT PRIMARY KEY REFERENCES content(content_id),
                      product_id BIGINT NOT NULL, rating SMALLINT NOT NULL);
CREATE TABLE article (content_id BIGINT PRIMARY KEY REFERENCES content(content_id),
                      title VARCHAR(200) NOT NULL);
CREATE TABLE comment (comment_id BIGINT PRIMARY KEY,
                      content_id BIGINT NOT NULL REFERENCES content(content_id),
                      body TEXT);

-- 테이블 분할
orders_2024, orders_2025, orders_2026 ...
 
-- 컬럼 분할
CREATE TABLE product_monthly_sales (
  product_id BIGINT,
  sales_year INT,
  m01 NUMERIC, m02 NUMERIC, /* ... */ m12 NUMERIC
);

-- PostgreSQL 선언적 파티셔닝
CREATE TABLE orders (
  order_id   BIGINT NOT NULL,
  ordered_at TIMESTAMP NOT NULL,
  /* ... */
  PRIMARY KEY (order_id, ordered_at)
) PARTITION BY RANGE (ordered_at);
 
CREATE TABLE orders_2026 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- 안티패턴
CREATE TABLE product (product_id BIGINT PRIMARY KEY, price FLOAT);
 
-- 0.1을 10번 더해도 정확히 1.0이 아닐 수 있음
SELECT SUM(price) FROM order_item WHERE order_id = :id;

CREATE TABLE product (product_id BIGINT PRIMARY KEY, price NUMERIC(12, 2) NOT NULL);

-- MySQL InnoDB
CREATE TABLE orders (
  order_id BINARY(16) PRIMARY KEY,   -- UUIDv4
  /* ... */
);

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  status   ENUM('PENDING', 'PAID', 'SHIPPED', 'DELIVERED', 'CANCELLED')   -- MySQL
);

CREATE TABLE order_status (
  status_code VARCHAR(20) PRIMARY KEY,
  label_ko    VARCHAR(50) NOT NULL,     -- '결제 완료', '배송중'
  sort_order  INT NOT NULL,
  is_active   BOOLEAN NOT NULL DEFAULT TRUE
);
-- 자식 컬럼이 ENUM인 채로 FK를 걸면 부모(VARCHAR(20))와 타입이 달라 거부된다.
-- 먼저 컬럼 타입을 맞춘다.
ALTER TABLE orders MODIFY status VARCHAR(20) NOT NULL;
 
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_status FOREIGN KEY (status) REFERENCES order_status(status_code);

CREATE TABLE product_image (
  image_id    BIGINT PRIMARY KEY,
  product_id  BIGINT NOT NULL REFERENCES product(product_id),
  object_key  VARCHAR(300) NOT NULL UNIQUE,   -- 'products/2026/09/abc123.webp'
  sort_order  INT NOT NULL,
  deleted_at  TIMESTAMP
);

-- PostgreSQL: 한 번도 사용되지 않은 인덱스
SELECT relname, indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
 
-- MySQL (sys 스키마)
SELECT * FROM sys.schema_unused_indexes;

SELECT * FROM customer
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- 안전한 형태: NOT EXISTS (안티 조인)
SELECT * FROM customer c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- 의도와 다름: total_amount는 최신 주문이 아닌 임의의 행에서 올 수 있음
SELECT customer_id, MAX(ordered_at), total_amount
FROM orders
GROUP BY customer_id;

SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;
 
-- PostgreSQL 전용: 더 간결한 DISTINCT ON
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, ordered_at DESC;

-- ordered_at 인덱스를 사용할 수 없음
SELECT * FROM orders WHERE YEAR(ordered_at) = 2026;
SELECT * FROM customer WHERE UPPER(email) = 'KIM@EXAMPLE.COM';
SELECT * FROM orders WHERE ordered_at + INTERVAL '7 day' > NOW();
 
-- 범위 조건으로 재작성
SELECT * FROM orders
WHERE ordered_at >= '2026-01-01' AND ordered_at < '2027-01-01';
 
SELECT * FROM orders
WHERE ordered_at > NOW() - INTERVAL '7 day';

-- order_no 컬럼은 VARCHAR인데 숫자로 비교
SELECT * FROM orders WHERE order_no = 20260916001;

SELECT * FROM product
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

-- "오늘의 랜덤 추천 상품"
SELECT * FROM product ORDER BY RAND() LIMIT 1;   -- PostgreSQL은 RANDOM()

-- 근사 샘플링
SELECT * FROM product TABLESAMPLE SYSTEM (1);   -- PostgreSQL, SQL Server(TABLESAMPLE)
SELECT * FROM product SAMPLE (1);                -- Oracle
 
-- ID 범위에서 난수를 뽑아 조회
SELECT * FROM product
WHERE product_id >= :random_id
ORDER BY product_id
LIMIT 1;

SELECT * FROM product WHERE name LIKE '%무선 이어폰%';

-- PostgreSQL: 트라이그램 인덱스
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_product_name_trgm ON product USING GIN (name gin_trgm_ops);
 
-- MySQL: ngram 파서
CREATE FULLTEXT INDEX ft_product_name ON product(name) WITH PARSER ngram;
SELECT * FROM product WHERE MATCH(name) AGAINST('무선 이어폰' IN BOOLEAN MODE);

SELECT p.product_id,
       COUNT(r.review_id)  AS review_count,
       SUM(oi.quantity)    AS sold_qty
FROM product p
LEFT JOIN review     r  ON r.product_id  = p.product_id
LEFT JOIN order_item oi ON oi.product_id = p.product_id
GROUP BY p.product_id;

-- 해법: 각각 먼저 집계한 뒤 조인
SELECT p.product_id,
       COALESCE(r.cnt, 0) AS review_count,
       COALESCE(s.qty, 0) AS sold_qty
FROM product p
LEFT JOIN (SELECT product_id, COUNT(*) AS cnt
           FROM review GROUP BY product_id) r ON r.product_id = p.product_id
LEFT JOIN (SELECT product_id, SUM(quantity) AS qty
           FROM order_item GROUP BY product_id) s ON s.product_id = p.product_id;

-- 목록 화면인데 상세 설명(TEXT), 스펙(JSON)까지 모두 가져옴
SELECT * FROM product WHERE category_id = 12;

List<Order> orders = orderRepository.findByCustomerId(customerId);  // 1번
for (Order order : orders) {
    order.getOrderItems().size();   // 주문 수만큼 N번 (LAZY 로딩)
}

@Query("select o from Order o join fetch o.orderItems where o.customerId = :customerId")
List<Order> findWithItems(@Param("customerId") Long customerId);

// 안티패턴
String sql = "SELECT * FROM product WHERE name LIKE '%" + keyword + "%'";
 
// 바인드 변수 사용
String sql = "SELECT * FROM product WHERE name LIKE ?";
ps.setString(1, "%" + keyword + "%");

Map<String, String> SORT_COLUMNS = Map.of(
    "price", "price",
    "newest", "created_at",
    "popular", "sold_count"
);
String orderBy = SORT_COLUMNS.getOrDefault(sortParam, "created_at");

-- 워커 여러 대가 같은 작업을 집어감
SELECT * FROM notification_job WHERE status = 'READY' ORDER BY job_id LIMIT 1;
UPDATE notification_job SET status = 'SENDING' WHERE job_id = :id;

@Transactional
public void placeOrder(OrderRequest req) {
    Order order = orderRepository.save(Order.from(req));
    stockService.decrease(req.getItems());
    paymentClient.approve(order);        // 외부 PG사 API 호출: 수 초 ~ 수십 초
    mailClient.sendOrderConfirmation();  // 외부 메일 서버 호출
}

ALTER TABLE customer ADD COLUMN deleted_at TIMESTAMP;
-- 이후 모든 쿼리에 WHERE deleted_at IS NULL

-- PostgreSQL: 부분 인덱스
CREATE UNIQUE INDEX ux_customer_email_active
  ON customer(email) WHERE deleted_at IS NULL;
 
-- SQL Server: 필터 인덱스
CREATE UNIQUE INDEX ux_customer_email_active
  ON customer(email) WHERE deleted_at IS NULL;
 
-- Oracle: 모든 키가 NULL인 행은 인덱싱되지 않는 성질을 이용
CREATE UNIQUE INDEX ux_customer_email_active
  ON customer (CASE WHEN deleted_at IS NULL THEN email END);
 
-- MySQL: 부분 인덱스가 없으므로 생성 컬럼 + 다중 NULL 허용 성질을 이용
ALTER TABLE customer
  ADD COLUMN active_flag TINYINT
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL,
  ADD UNIQUE INDEX ux_customer_email_active (email, active_flag);

-- (1) Single Table Inheritance: 한 테이블에 모두
CREATE TABLE product (
  product_id      BIGINT PRIMARY KEY,
  product_type    VARCHAR(20) NOT NULL,   -- APPAREL / ELECTRONICS / BOOK
  name            VARCHAR(200) NOT NULL,
  price           NUMERIC(12,2) NOT NULL,
  size_label      VARCHAR(10),   -- APPAREL 전용
  material        VARCHAR(50),   -- APPAREL 전용
  warranty_months INT,           -- ELECTRONICS 전용
  isbn            VARCHAR(13),   -- BOOK 전용
  CONSTRAINT ck_book_isbn CHECK (product_type <> 'BOOK' OR isbn IS NOT NULL)
);
 
-- (2) Class Table Inheritance: 공통 테이블 + 유형별 1:1 테이블
CREATE TABLE product (
  product_id   BIGINT PRIMARY KEY,
  product_type VARCHAR(20) NOT NULL,
  name         VARCHAR(200) NOT NULL,
  price        NUMERIC(12,2) NOT NULL
);
CREATE TABLE apparel (
  product_id BIGINT PRIMARY KEY REFERENCES product(product_id),
  size_label VARCHAR(10) NOT NULL,
  material   VARCHAR(50) NOT NULL
);
CREATE TABLE electronics (
  product_id      BIGINT PRIMARY KEY REFERENCES product(product_id),
  warranty_months INT NOT NULL
);
CREATE TABLE book (
  product_id BIGINT PRIMARY KEY REFERENCES product(product_id),
  isbn       VARCHAR(13) NOT NULL UNIQUE,
  author     VARCHAR(100) NOT NULL
);
 
-- (3) Concrete Table Inheritance: 유형별 완전 독립 테이블
CREATE TABLE apparel (apparel_id BIGINT PRIMARY KEY, name VARCHAR(200), price NUMERIC(12,2),
                      size_label VARCHAR(10) NOT NULL, material VARCHAR(50) NOT NULL);
CREATE TABLE book    (book_id BIGINT PRIMARY KEY, name VARCHAR(200), price NUMERIC(12,2),
                      isbn VARCHAR(13) NOT NULL, author VARCHAR(100) NOT NULL);

-- Path Enumeration: 경로를 문자열로 저장
CREATE TABLE category (
  category_id BIGINT PRIMARY KEY,
  path        VARCHAR(500) NOT NULL,   -- '/1/4/7/'  (가전 > TV > OLED TV)
  name        VARCHAR(100)
);
SELECT * FROM category WHERE path LIKE '/1/4/%';   -- TV 하위 전체 (앞쪽 고정이라 인덱스 사용 가능)
 
-- Nested Sets: 트리를 좌우 번호로 인코딩
CREATE TABLE category (
  category_id BIGINT PRIMARY KEY,
  lft INT NOT NULL,
  rgt INT NOT NULL,
  name VARCHAR(100)
);
SELECT c.* FROM category c, category root
WHERE root.category_id = :id AND c.lft BETWEEN root.lft AND root.rgt;
 
-- Closure Table: 모든 조상-자손 쌍을 저장
CREATE TABLE category_path (
  ancestor_id   BIGINT NOT NULL REFERENCES category(category_id),
  descendant_id BIGINT NOT NULL REFERENCES category(category_id),
  depth         INT NOT NULL,
  PRIMARY KEY (ancestor_id, descendant_id)
);
CREATE INDEX ix_cp_desc ON category_path(descendant_id);

-- '가전' 하위 모든 카테고리의 상품 수
SELECT COUNT(DISTINCT pc.product_id)
FROM category_path cp
JOIN product_category pc ON pc.category_id = cp.descendant_id
WHERE cp.ancestor_id = :electronics_id;
 
-- 브레드크럼(가전 > TV > OLED TV): 조상 경로 조회
SELECT c.name
FROM category_path cp
JOIN category c ON c.category_id = cp.ancestor_id
WHERE cp.descendant_id = :oled_tv_id
ORDER BY cp.depth DESC;
 
-- 새 카테고리 추가: 부모의 모든 조상 경로를 복사 + 자기 자신
INSERT INTO category_path (ancestor_id, descendant_id, depth)
SELECT ancestor_id, :new_id, depth + 1
FROM category_path WHERE descendant_id = :parent_id
UNION ALL
SELECT :new_id, :new_id, 0;

-- 유효 기간 모델 (SCD Type 2)
CREATE TABLE product_price_history (
  product_id BIGINT NOT NULL REFERENCES product(product_id),
  price      NUMERIC(12,2) NOT NULL,
  valid_from TIMESTAMP NOT NULL,
  valid_to   TIMESTAMP NOT NULL DEFAULT '9999-12-31',
  PRIMARY KEY (product_id, valid_from)
);
 
-- 특정 시점 가격
SELECT price FROM product_price_history
WHERE product_id = :id
  AND :as_of >= valid_from
  AND :as_of <  valid_to;
 
-- 가격 변경: 기존 구간을 닫고 새 구간 삽입 (한 트랜잭션)
UPDATE product_price_history
SET valid_to = :changed_at
WHERE product_id = :id AND valid_to = '9999-12-31';
 
INSERT INTO product_price_history (product_id, price, valid_from)
VALUES (:id, :new_price, :changed_at);

-- PostgreSQL: 같은 상품의 가격 구간이 겹치지 않도록 DB가 보장
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE product_price_history
  ADD CONSTRAINT ex_price_no_overlap
  EXCLUDE USING gist (product_id WITH =, tsrange(valid_from, valid_to) WITH &&);

CREATE TABLE audit_log (
  audit_id    BIGINT PRIMARY KEY,
  table_name  VARCHAR(50)  NOT NULL,
  row_pk      VARCHAR(100) NOT NULL,
  operation   CHAR(1)      NOT NULL,   -- I / U / D
  before_data JSONB,
  after_data  JSONB,
  changed_by  VARCHAR(50)  NOT NULL,
  changed_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX ix_audit_row ON audit_log(table_name, row_pk, changed_at);

-- PostgreSQL: 트랜잭션 시작 시 애플리케이션이 설정
SET LOCAL app.user_id = 'admin_kim';
-- 트리거 안에서 읽기
current_setting('app.user_id', true)

ALTER TABLE product ADD COLUMN version INT NOT NULL DEFAULT 0;
 
UPDATE product
SET    price = :price, version = version + 1
WHERE  product_id = :id
  AND  version = :read_version;
-- 영향받은 행 수가 0이면 다른 사람이 먼저 수정한 것 → "최신 내용을 다시 불러오세요"

UPDATE product_stock
SET    quantity = quantity - :qty
WHERE  product_id = :id
  AND  quantity >= :qty;
-- 영향받은 행 수가 0이면 재고 부족

-- 첫 페이지
SELECT product_id, name, price, created_at
FROM product
ORDER BY created_at DESC, product_id DESC
LIMIT 20;
 
-- 다음 페이지: 직전 페이지 마지막 행의 (created_at, product_id)를 커서로 전달
SELECT product_id, name, price, created_at
FROM product
WHERE created_at < :last_created_at
   OR (created_at = :last_created_at AND product_id < :last_product_id)
ORDER BY created_at DESC, product_id DESC
LIMIT 20;
 
-- 필요 인덱스
CREATE INDEX ix_product_cursor ON product(created_at DESC, product_id DESC);

CREATE TABLE notification_job (
  job_id     BIGINT PRIMARY KEY,
  order_id   BIGINT NOT NULL,
  job_type   VARCHAR(30) NOT NULL,   -- ORDER_CONFIRMED, SHIPPED ...
  status     VARCHAR(10) NOT NULL DEFAULT 'READY',
  locked_by  VARCHAR(50),
  locked_at  TIMESTAMP,
  attempts   INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- PostgreSQL: 처리 대기 작업 10건을 원자적으로 가져오기
UPDATE notification_job
SET status = 'RUNNING', locked_by = :worker, locked_at = NOW(), attempts = attempts + 1
WHERE job_id IN (
  SELECT job_id FROM notification_job
  WHERE status = 'READY'
  ORDER BY job_id
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
RETURNING *;
 
-- SQL Server
UPDATE TOP (10) notification_job WITH (ROWLOCK, READPAST, UPDLOCK)
SET status = 'RUNNING', locked_by = @worker, locked_at = SYSUTCDATETIME()
OUTPUT inserted.*
WHERE status = 'READY';

CREATE TABLE idempotency_key (
  idem_key     VARCHAR(64) PRIMARY KEY,   -- 클라이언트가 요청마다 생성한 UUID
  request_hash CHAR(64) NOT NULL,          -- 요청 본문의 해시
  response     JSONB,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- 결제 처리와 같은 트랜잭션에서 (PostgreSQL)
INSERT INTO idempotency_key (idem_key, request_hash)
VALUES (:key, :hash)
ON CONFLICT (idem_key) DO NOTHING;
-- 영향 행 수가 0이면 이미 처리된 요청 → 저장된 response를 그대로 반환

-- 집계 테이블
CREATE TABLE product_sales_daily (
  sales_date  DATE   NOT NULL,
  product_id  BIGINT NOT NULL,
  order_count INT    NOT NULL,
  sold_qty    INT    NOT NULL,
  revenue     NUMERIC(14,2) NOT NULL,
  PRIMARY KEY (sales_date, product_id)
);
 
-- 배치: 전날 주문을 집계해 UPSERT (PostgreSQL)
INSERT INTO product_sales_daily (sales_date, product_id, order_count, sold_qty, revenue)
SELECT DATE(o.ordered_at), oi.product_id,
       COUNT(DISTINCT o.order_id), SUM(oi.quantity), SUM(oi.quantity * oi.unit_price)
FROM orders o
JOIN order_item oi ON oi.order_id = o.order_id
WHERE o.ordered_at >= :day AND o.ordered_at < :day + INTERVAL '1 day'
  AND o.status <> 'CANCELLED'
GROUP BY DATE(o.ordered_at), oi.product_id
ON CONFLICT (sales_date, product_id) DO UPDATE
SET order_count = EXCLUDED.order_count,
    sold_qty    = EXCLUDED.sold_qty,
    revenue     = EXCLUDED.revenue;