18주차 코드 뒤풀이: 다섯 SQL을 증거 경계까지 읽기

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:

param(
 [ValidateSet('Compose','Container')][string]$Mode='Compose',[string]$Container='',
 [string]$ComposeProject='w18-project-sql-lab',[Parameter(Mandatory=$true)][string]$SqlRoot,
 [Parameter(Mandatory=$true)][string]$EvidenceDir
)

$ErrorActionPreference='Stop';$root=Split-Path -Parent $PSScriptRoot
if(-not [IO.Path]::IsPathRooted($SqlRoot)){throw 'SqlRoot must be an absolute packaged SQL path'}
if(-not [IO.Path]::IsPathRooted($EvidenceDir)){throw 'EvidenceDir must be an absolute learner-owned path'}
$sql=(Resolve-Path -LiteralPath $SqlRoot).Path;$e=[IO.Path]::GetFullPath($EvidenceDir);New-Item -ItemType Directory -Force $e|Out-Null
$compose=Join-Path $root 'compose.yaml';$owned=$false

try{
 if($Mode-eq'Compose'){
  if($Container){throw 'W18 Compose mode rejects -Container'};if([string]::IsNullOrWhiteSpace($env:FCL_DB_PASSWORD)){$env:FCL_DB_PASSWORD='w18-disposable-password'}
  & docker compose -f $compose -p $ComposeProject up -d --wait db;if($LASTEXITCODE-ne 0){throw "W18 compose up exit=$LASTEXITCODE"};$owned=$true;$Container=(& docker compose -f $compose -p $ComposeProject ps -q db).Trim()
 }elseif([string]::IsNullOrWhiteSpace($Container)){throw 'W18 Container mode requires -Container'}
 if([string]::IsNullOrWhiteSpace($Container)){throw 'W18 PostgreSQL container resolution failed'}

 $fixture='fixture.sql';Get-Content -Raw (Join-Path $sql $fixture)|docker exec -i $Container psql -v ON_ERROR_STOP=1 -U app -d financial_core;if($LASTEXITCODE-ne 0){throw "W18 fixture exit=$LASTEXITCODE"}

 $files=@('01-reconcile.sql','02-anti-join.sql','03-aggregation.sql','04-running-balance.sql','05-keyset.sql');$results=@();for($i=0;$i-lt 5;$i++){$out=@(Get-Content -Raw (Join-Path $sql $files[$i])|docker exec -i $Container psql -At -v ON_ERROR_STOP=1 -U app -d financial_core 2>&1);$exit=$LASTEXITCODE;if($exit-ne 0-or($out-join"`n")-cnotmatch "W18_Q$($i+1) "){throw "W18 file=$($files[$i]) exit=$exit output=$out"};$results+=[pscustomobject]@{ordinal=$i+1;file=$files[$i];native_exit=$exit;marker=($out|Where-Object{$_-match'^W18_Q'}|Select-Object -Last 1);sha256=(Get-FileHash -Algorithm SHA256 (Join-Path $sql $files[$i])).Hash.ToLowerInvariant()}}

 $hashes=@($fixture)+$files|ForEach-Object{[pscustomobject]@{file=$_;sha256=(Get-FileHash -Algorithm SHA256 (Join-Path $sql $_)).Hash.ToLowerInvariant()}};$manifest=[ordered]@{scope='PROJECT_SQL_RETRIEVAL_NOT_SQLD_MOCK';files=5;native_exits=0;rows=5;cleanup=1;results=$results;hashes=@($hashes)};$manifest|ConvertTo-Json -Depth 5|Set-Content -Encoding utf8 (Join-Path $e 'project-sql5-manifest.json');"W18_PROJECT_SQL5_GREEN files=5 native_exits=0 rows=5 hashes=6 cleanup=1"

}finally{if($owned){& docker compose -f $compose -p $ComposeProject down -v|Out-Null;if($LASTEXITCODE-ne 0){throw 'W18 compose cleanup failed'}}}

\set ON_ERROR_STOP on
DROP SCHEMA IF EXISTS w18_project CASCADE; CREATE SCHEMA w18_project;

CREATE TABLE w18_project.account(id bigint PRIMARY KEY,balance bigint NOT NULL);
CREATE TABLE w18_project.ledger(id bigint PRIMARY KEY,account_id bigint NOT NULL REFERENCES w18_project.account(id),signed_amount bigint NOT NULL,created_at timestamptz NOT NULL);

INSERT INTO w18_project.account VALUES(1,70),(2,0),(3,5);
INSERT INTO w18_project.ledger VALUES(1,1,100,'2026-01-01Z'),(2,1,-30,'2026-01-02Z'),(3,3,5,'2026-01-01Z');

DO $g$ BEGIN IF (SELECT count(*) FROM w18_project.account a LEFT JOIN(SELECT account_id,sum(signed_amount)b FROM w18_project.ledger GROUP BY account_id)l ON l.account_id=a.id WHERE a.balance<>coalesce(l.b,0))<>0 THEN RAISE EXCEPTION 'reconcile';END IF;END $g$; SELECT 'W18_Q1 rows=0';

DO $g$ BEGIN IF (SELECT array_agg(id ORDER BY id) FROM w18_project.account a WHERE NOT EXISTS(SELECT 1 FROM w18_project.ledger l WHERE l.account_id=a.id))<>ARRAY[2::bigint] THEN RAISE EXCEPTION 'anti join';END IF;END $g$; SELECT 'W18_Q2 rows=1 id=2';

DO $g$ DECLARE r record;BEGIN SELECT count(*) n,coalesce(sum(signed_amount)FILTER(WHERE signed_amount>0),0)p,coalesce(-sum(signed_amount)FILTER(WHERE signed_amount<0),0)d INTO r FROM w18_project.ledger WHERE account_id=1;IF r.n<>2 OR r.p<>100 OR r.d<>30 THEN RAISE EXCEPTION 'aggregation';END IF;END $g$; SELECT 'W18_Q3 rows=1 credits=100 debits=30';

DO $g$ DECLARE x bigint;BEGIN SELECT running INTO x FROM(SELECT id,sum(signed_amount)OVER(PARTITION BY account_id ORDER BY created_at,id)running FROM w18_project.ledger WHERE account_id=1)s ORDER BY id DESC LIMIT 1;IF x<>70 THEN RAISE EXCEPTION 'running';END IF;END $g$; SELECT 'W18_Q4 rows=2 final=70';

DO $g$ DECLARE ids bigint[];BEGIN SELECT array_agg(id ORDER BY created_at DESC,id DESC) INTO ids FROM(SELECT * FROM w18_project.ledger WHERE account_id=1 AND(created_at,id)<('2026-01-03Z'::timestamptz,99)ORDER BY created_at DESC,id DESC LIMIT 2)s;IF ids<>ARRAY[2::bigint,1::bigint] THEN RAISE EXCEPTION 'keyset';END IF;END $g$; SELECT 'W18_Q5 rows=2 ids=2|1';

-- W18-SQL-Q27 illustrative example; not a shipped workbook answer.
-- Assumption: every business_tx status and tx_type, including OPENING, counts as a transaction.
-- No-transaction customers remain with NULL first/last dates.
SET search_path TO :"workbook_schema", public;

SELECT
    c.customer_id,
    MIN(t.business_date) AS first_transaction_date,
    MAX(t.business_date) AS last_transaction_date
FROM customer AS c

LEFT JOIN account AS a ON a.customer_id = c.customer_id
LEFT JOIN business_tx AS t ON t.account_id = a.account_id
GROUP BY c.customer_id
ORDER BY c.customer_id;

-- Oracle: customer 1 = 2026-06-30..2027-01-02; customer 2 = 2026-06-30..2027-01-02.
-- customer 3 = 2026-06-30..2026-06-30; customer 4 = NULL..NULL.
-- customer 5 = NULL..NULL; customer 6 = 2026-06-30..2026-06-30.

-- W18-SQL-Q28 illustrative example; not a shipped workbook answer.
-- Assumption: a streak is consecutive inside actor_id order (occurred_at, tx_id).
-- Every non-FAILED row closes the preceding FAILED island.
SET search_path TO :"workbook_schema", public;

WITH ordered AS (
    SELECT
        actor_id,
        tx_id,
        status,
        occurred_at,
        COUNT(*) FILTER (WHERE status <> 'FAILED') OVER (
            PARTITION BY actor_id
            ORDER BY occurred_at, tx_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS reset_group
    FROM business_tx

), failed_streaks AS (
    SELECT
        actor_id,
        reset_group,
        COUNT(*) AS streak_length,
        MIN(occurred_at) AS first_failed_at,
        MAX(occurred_at) AS last_failed_at
    FROM ordered
    WHERE status = 'FAILED'
    GROUP BY actor_id, reset_group
    HAVING COUNT(*) >= 3
)

SELECT actor_id, streak_length, first_failed_at, last_failed_at
FROM failed_streaks
ORDER BY actor_id, first_failed_at;
-- Oracle: USER-FAIL|3|2026-12-28 10:00:00+09|2026-12-28 10:02:00+09.