SQL & Database Engineering

-- ============================================================
--  Automated ETL Procedure: 3GPP CDR Staging to Core
--  Platform: Oracle 19c | Context: AT&T / Verizon BSS
-- ============================================================

CREATE OR REPLACE PROCEDURE etl_load_cdr_records (
    p_batch_date   IN  DATE     DEFAULT SYSDATE,
    p_rows_loaded  OUT NUMBER
)
AS
    v_batch_id     NUMBER;
    v_count        PLS_INTEGER := 0;
    ex_no_data     EXCEPTION;

BEGIN
    -- Generate batch identifier
    SELECT etl_batch_seq.NEXTVAL INTO v_batch_id FROM DUAL;

    -- Bulk insert from staging to core via MERGE
    MERGE INTO cdr_core tgt
    USING (
        SELECT record_id, msisdn, call_type, duration_sec,
               TRUNC(call_ts) call_date, node_id
        FROM   cdr_staging
        WHERE  load_date = TRUNC(p_batch_date)
        AND    status = 'PENDING'
    ) src
    ON (tgt.record_id = src.record_id)
    WHEN MATCHED THEN UPDATE SET
        tgt.status = 'RELOADED', tgt.batch_id = v_batch_id
    WHEN NOT MATCHED THEN INSERT
        (record_id, msisdn, call_type, duration_sec,
         call_date, node_id, batch_id, status)
    VALUES
        (src.record_id, src.msisdn, src.call_type, src.duration_sec,
         src.call_date, src.node_id, v_batch_id, 'LOADED');

    v_count := SQL%ROWCOUNT;
    IF v_count = 0 THEN RAISE ex_no_data; END IF;

    UPDATE cdr_staging
       SET status = 'PROCESSED', batch_id = v_batch_id
     WHERE load_date = TRUNC(p_batch_date)
       AND status = 'PENDING';

    p_rows_loaded := v_count;
    COMMIT;

EXCEPTION
    WHEN ex_no_data THEN
        DBMS_OUTPUT.PUT_LINE('No records for batch date: ' || p_batch_date);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END etl_load_cdr_records;
/

-- ============================================================
--  Billing Audit Query: SOX-Compliant Revenue Reconciliation
--  Platform: Oracle | Context: AT&T Cingular Billing Cycles
-- ============================================================

WITH daily_revenue AS (
    SELECT
        TRUNC(txn_timestamp, 'DD')          AS txn_date,
        account_type,
        SUM(charge_amount)                  AS gross_revenue,
        SUM(discount_amount)                AS total_discounts,
        COUNT(DISTINCT subscriber_id)       AS active_subs,
        SUM(CASE WHEN status = 'FAILED'
                 THEN charge_amount ELSE 0 END) AS failed_revenue
    FROM   billing_transactions
    WHERE  txn_timestamp >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
    GROUP BY
        TRUNC(txn_timestamp, 'DD'), account_type
),
variance_check AS (
    SELECT
        d.*,
        LAG(gross_revenue, 1) OVER
            (PARTITION BY account_type ORDER BY txn_date) AS prev_day_rev,
        ROUND(
            (gross_revenue - LAG(gross_revenue,1)
                OVER(PARTITION BY account_type ORDER BY txn_date))
            / NULLIF(LAG(gross_revenue,1)
                OVER(PARTITION BY account_type ORDER BY txn_date), 0)
            * 100, 2
        ) AS day_over_day_pct
    FROM daily_revenue d
)
SELECT
    txn_date,
    account_type,
    TO_CHAR(gross_revenue, '$999,999,990.00')  AS gross_rev,
    TO_CHAR(total_discounts, '$999,999,990.00') AS discounts,
    active_subs,
    day_over_day_pct                              AS dod_variance_pct,
    CASE WHEN ABS(day_over_day_pct) > 15
         THEN '⚠ FLAG FOR REVIEW'
         ELSE 'OK'
    END AS audit_status
FROM  variance_check
WHERE failed_revenue / NULLIF(gross_revenue, 0) > 0.02
   OR  ABS(day_over_day_pct) > 15
ORDER BY txn_date DESC, account_type;

-- ============================================================
--  Performance Tuning: Index Strategy + Query Rewrite
--  Platform: SQL Server | Context: Verizon App Support
-- ============================================================

-- BEFORE: full table scan on 200M-row incident table (12.3s)
-- SELECT * FROM incidents WHERE app_id = 'VZ-CORE' AND sev = 1

-- STEP 1: Composite covering index
CREATE NONCLUSTERED INDEX IX_incidents_app_sev_ts
    ON dbo.incidents (app_id, severity_level, created_ts DESC)
    INCLUDE (incident_id, title, assigned_team, resolved_ts)
WITH (ONLINE = ON, FILLFACTOR = 85);

-- STEP 2: Rewritten query using CTE + filtered window
WITH recent_sev1 AS (
    SELECT
        incident_id,
        title,
        assigned_team,
        created_ts,
        resolved_ts,
        DATEDIFF(MINUTE, created_ts, ISNULL(resolved_ts, GETDATE())) AS mttr_min,
        ROW_NUMBER() OVER (
            PARTITION BY assigned_team
            ORDER BY     created_ts DESC
        ) AS rn
    FROM   dbo.incidents WITH (NOLOCK)
    WHERE  app_id         = N'VZ-CORE'
      AND  severity_level = 1
      AND  created_ts    >= DATEADD(DAY, -30, GETDATE())
)
SELECT
    assigned_team,
    COUNT(*)                                     AS incident_count,
    AVG(CAST(mttr_min AS DECIMAL(10,2)))         AS avg_mttr_min,
    MAX(mttr_min)                                AS worst_mttr_min,
    MIN(CASE WHEN resolved_ts IS NULL
             THEN 1 ELSE 0 END)               AS open_incidents
FROM  recent_sev1
GROUP BY assigned_team
HAVING   COUNT(*) > 2
ORDER BY avg_mttr_min ASC;
-- AFTER: 0.4s execution — 30x improvement using covering index