Maximize Prime Item Inventory: Amazon SQL Interview Question

문제 풀이 링크: https://datalemur.com/questions/prime-warehouse-storage

Amazon SQL Interview Question | DataLemur

---

🏬 아마존 창고 최적화 문제 요약

• 목적: 500,000 제곱피트의 창고 공간을 최대한 효율적으로 사용
• 조건: 프라임 상품 배치(prime_eligible)를 가능한 많이 넣고, 남은 공간에 논프라임 배치(not_prime) 추가
  • 각 배치는 여러 상품으로 구성된 세트 단위이며, 분리해서 넣을 수 없음 (prime_eligible과 not_prime이 각 1세트 → 총 2세트)
  • 논프라임 배치는 반드시 1개 이상 포함되어야 함
  • 모든 계산은 정수 단위로 이루어져야함

👉 목표: 프라임 배치를 최대한 넣고, 남은 공간에 논프라임 배치를 최대한 넣는 것!

---

1. summary CTE

WITH summary AS (  
  SELECT  
    item_type,  
    SUM(square_footage) AS total_sqft,  
    COUNT(*) AS item_count  
  FROM inventory  
  GROUP BY item_type
)

• item_type별로:
  • 총 면적 합(total_sqft)
  • 아이템 수(item_count)
• 즉, prime_eligible과 not_prime 각각이 전체 몇 개이며, 총 면적이 얼마인지 요약하는 임시 테이블을 만드는 부분

 

2. prime_occupied_area CTE

prime_occupied_area AS (  
  SELECT  
    item_type,
    total_sqft,
    FLOOR(500000/total_sqft) AS prime_item_batch_count,
    (FLOOR(500000/total_sqft) * item_count) AS prime_item_count
  FROM summary  
  WHERE item_type = 'prime_eligible'
)

• prime_eligible만 대상으로:
  • 배치 1세트가 차지하는 면적(total_sqft)
  • 프라임 배치를 최대 몇 개까지 넣을 수 있는지 계산 → FLOOR(500000 / total_sqft)
  • 그 배치 수만큼 넣었을 때 총 몇 개 아이템이 들어가는지 → 배치 수 × 아이템 수

3. 최종 SELECT

SELECT
  item_type,
  CASE 
    WHEN item_type = 'prime_eligible' 
      THEN (FLOOR(500000/total_sqft) * item_count)
    WHEN item_type = 'not_prime' 
      THEN FLOOR((500000 - (SELECT FLOOR(500000/total_sqft) * total_sqft FROM prime_occupied_area)) / total_sqft) * item_count
  END AS item_count
FROM summary
ORDER BY item_type DESC;

• summary 테이블을 기준으로:
  • ✅ prime_eligible일 경우:
    • 앞에서와 동일하게 프라임 배치 최대 개수 × 각 배치당 아이템 수
  • ✅ not_prime일 경우:
    • 전체 500,000 공간에서 프라임 배치들이 차지한 면적을 빼고
    • 남은 공간을 가지고 논프라임 배치를 최대 몇 개 넣을 수 있는지 계산
    • 그 배치 수 × 배치당 아이템 수 = 총 not_prime 아이템 수

---

최종 결과

WITH summary AS ( -- summary CTE
  SELECT item_type, 
    SUM(square_footage) AS total_sqft,
    COUNT(*) AS item_count
  FROM inventory
  GROUP BY item_type
  ),
  prime_occupied_area AS ( -- prime_occupied_area CTE
    SELECT item_type, 
      total_sqft,
      FLOOR(500000/total_sqft) AS prime_item_batch_count, 
      (FLOOR(500000/total_sqft) * item_count) AS prime_item_count
    FROM summary
    WHERE item_type = 'prime_eligible'
  )
-- 최종 SELECT
SELECT item_type, 
  CASE 
    WHEN item_type = 'prime_eligible'
        THEN (FLOOR(500000/total_sqft) * item_count)
    WHEN item_type = 'not_prime'
        THEN FLOOR((500000 - (SELECT FLOOR(500000/total_sqft) * total_sqft FROM prime_occupied_area)) / total_sqft) * item_count
  END AS item_count
FROM summary
ORDER BY item_type DESC;