16주차 코드 — SQL 논리 순서·JOIN·NULL·집계

services:
  db:
    image: postgres:17.10-alpine
    ports:
      - "${FCL_DB_PORT:-5432}:5432"
    environment:
      POSTGRES_DB: financial_core
      POSTGRES_USER: app
      POSTGRES_PASSWORD: ${FCL_DB_PASSWORD:?set FCL_DB_PASSWORD in this terminal}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d financial_core"]
      interval: 2s
      timeout: 2s
      retries: 30
    volumes:
      - financial-core-db:/var/lib/postgresql/data

volumes:
  financial-core-db:

\set ON_ERROR_STOP on
DO $ddl$ BEGIN IF EXISTS(SELECT 1 FROM pg_namespace WHERE nspname='w16_project') THEN EXECUTE 'DROP SCHEMA w16_project CASCADE'; END IF; END $ddl$;
CREATE SCHEMA w16_project;
CREATE TABLE w16_project.account(id bigint PRIMARY KEY,balance bigint NOT NULL);
CREATE TABLE w16_project.ledger_entry(id bigint PRIMARY KEY,account_id bigint REFERENCES w16_project.account(id),signed_amount bigint NOT NULL,created_at timestamptz NOT NULL);
INSERT INTO w16_project.account VALUES (1,100),(2,50),(3,0);
INSERT INTO w16_project.ledger_entry VALUES (1,1,100,'2026-01-01Z'),(2,2,70,'2026-01-01Z'),(3,2,-20,'2026-01-02Z');

\set ON_ERROR_STOP on
DO $q$ BEGIN IF (SELECT count(*) FROM (SELECT id FROM w16_project.account WHERE balance>0 ORDER BY balance DESC,id LIMIT 2)x)<>2 THEN RAISE EXCEPTION 'W16 Q1 rows'; END IF; END $q$;
SELECT 'W16_Q1 rows=2 top_id=1' WHERE (SELECT id FROM w16_project.account ORDER BY balance DESC,id LIMIT 1)=1;

\set ON_ERROR_STOP on
DO $q$ BEGIN IF (SELECT count(*) FROM w16_project.account a WHERE NOT EXISTS(SELECT 1 FROM w16_project.ledger_entry l WHERE l.account_id=a.id))<>1 THEN RAISE EXCEPTION 'W16 Q2 rows'; END IF; END $q$;
SELECT 'W16_Q2 rows=1 account_id=3' WHERE (SELECT id FROM w16_project.account a WHERE NOT EXISTS(SELECT 1 FROM w16_project.ledger_entry l WHERE l.account_id=a.id))=3;

\set ON_ERROR_STOP on
DO $q$ BEGIN IF EXISTS(SELECT a.id FROM w16_project.account a LEFT JOIN w16_project.ledger_entry l ON l.account_id=a.id GROUP BY a.id,a.balance HAVING a.balance<>coalesce(sum(l.signed_amount),0)) THEN RAISE EXCEPTION 'W16 Q3 mismatch'; END IF; END $q$;
SELECT 'W16_Q3 mismatches=0 ledger_sum=150' WHERE (SELECT sum(signed_amount) FROM w16_project.ledger_entry)=150;

param([Parameter(Mandatory=$true)][string]$SqlRoot,[Parameter(Mandatory=$true)][string]$EvidenceDir,[ValidateSet('Compose','Container')][string]$Mode='Compose',[string]$Container='',[string]$ComposeProject='w16-project-lab')
$ErrorActionPreference='Stop';$root=Split-Path -Parent $PSScriptRoot;$sql=(Resolve-Path -LiteralPath $SqlRoot).Path;$e=[IO.Path]::GetFullPath($EvidenceDir);New-Item -ItemType Directory -Force $e|Out-Null
$files=@('logical-order.sql','anti-join.sql','aggregation.sql');foreach($name in $files){if(!(Test-Path -LiteralPath (Join-Path $sql $name) -PathType Leaf)){throw "missing learner project SQL: $name"}}
$owned=$false;$compose=Join-Path $root 'compose.yaml'
try{
 if($Mode-eq'Compose'){if($Container){throw 'W16 Compose mode rejects Container'};if([string]::IsNullOrWhiteSpace($env:FCL_DB_PASSWORD)){$env:FCL_DB_PASSWORD='w16-disposable-password'};& docker compose -f $compose -p $ComposeProject up -d --wait db;if($LASTEXITCODE-ne 0){throw "W16 compose up exit=$LASTEXITCODE"};$owned=$true;$Container=(& docker compose -f $compose -p $ComposeProject ps -q db).Trim()}elseif(!$Container){throw 'W16 Container mode requires Container'}
 if([string]::IsNullOrWhiteSpace($Container)){throw 'W16 container resolution failed'}
 Get-Content -Raw (Join-Path $root 'sql/w16/fixture.sql')|docker exec -i $Container psql -v ON_ERROR_STOP=1 -U app -d financial_core|Out-Null;if($LASTEXITCODE-ne 0){throw 'W16 fixture failed'}
 $results=@();for($i=0;$i-lt 3;$i++){$path=Join-Path $sql $files[$i];$out=@(Get-Content -Raw $path|docker exec -i $Container psql -At -v ON_ERROR_STOP=1 -U app -d financial_core 2>&1);$exit=$LASTEXITCODE;$marker="W16_Q$($i+1) ";if($exit-ne 0-or($out-join"`n")-cnotmatch [regex]::Escape($marker)){throw "W16 SQL invariant failed file=$($files[$i]) exit=$exit output=$out"};$results+=[ordered]@{ordinal=$i+1;file=$files[$i];marker=($out|Where-Object{$_-like "$marker*"}|Select-Object -Last 1);native_exit=0;sha256=(Get-FileHash -Algorithm SHA256 $path).Hash.ToLowerInvariant()}}
 $manifest=[ordered]@{schema='w16_project';canonical_logical_order='logical-order.sql';files=3;rows=3;invariants=@('rows=2 top_id=1','rows=1 account_id=3','mismatches=0 ledger_sum=150');native_exits=0;hashes=3;cleanup=1;results=$results};$manifest|ConvertTo-Json -Depth 5|Set-Content -Encoding utf8 (Join-Path $e 'project-regression.json');"W16_PROJECT_SQL_GREEN files=3 rows=3 invariants=3 native_exits=0 hashes=3 cleanup=1"
}finally{if($Mode-eq'Compose'){& docker compose -f $compose -p $ComposeProject down -v|Out-Null}}

-- W16-SQL-Q23 illustrative example; not a shipped workbook answer.
-- Assumption: recent seven days is the half-open interval [fixture_clock.as_of - 7 days, fixture_clock.as_of).
-- All statuses are included because the prompt does not request a status filter; only customers with rows appear.
SET search_path TO :"workbook_schema", public;

WITH bounds AS (
    SELECT as_of - INTERVAL '7 days' AS window_start, as_of AS window_end
    FROM fixture_clock
), recent_tx AS (
    SELECT t.tx_id, t.account_id, t.amount, t.status
    FROM business_tx AS t
    CROSS JOIN bounds AS b
    WHERE t.occurred_at >= b.window_start
      AND t.occurred_at < b.window_end
)
SELECT
    a.customer_id,
    COUNT(*) AS tx_count,
    SUM(r.amount) AS total_amount
FROM recent_tx AS r
JOIN account AS a ON a.account_id = r.account_id
GROUP BY a.customer_id
ORDER BY a.customer_id;
-- Fixture oracle: customer 1 = 7 rows / 1,201,699; customer 2 = 4 rows / 3,303.

-- W16-SQL-Q24 illustrative example; not a shipped workbook answer.
-- Assumption: classify account.balance as SMALL <100000, MEDIUM <500000, otherwise LARGE.
-- The frozen 100000/499999/500000 balances execute both CASE boundaries.
SET search_path TO :"workbook_schema", public;

SELECT
    account_id,
    balance,
    CASE
        WHEN balance < 100000 THEN 'SMALL'
        WHEN balance < 500000 THEN 'MEDIUM'
        ELSE 'LARGE'
    END AS amount_band
FROM account
ORDER BY account_id;
-- Fixture oracle: SMALL=4, MEDIUM=2, LARGE=2; 100000=MEDIUM, 499999=MEDIUM, 500000=LARGE.