analysis_queries.sql

SELECT
    Class,
    COUNT(*) AS transaction_count,
    ROUND(
        100.0 * COUNT(*) / (SELECT COUNT(*) FROM transactions),
        4
    ) AS percentage
FROM transactions
GROUP BY Class;

-----------------------------------------------------------------

SELECT
    Class,
    ROUND(AVG(Amount), 2) AS avg_amount
FROM transactions
GROUP BY Class;


-----------------------------------------------------------------


SELECT
    CAST((Time / 3600) % 24 AS INTEGER) AS hour_of_day,
    COUNT(*) AS total_transactions,
    SUM(Class) AS fraud_transactions,
    ROUND(1.0 * SUM(Class) / COUNT(*), 5) AS fraud_rate
FROM transactions
GROUP BY hour_of_day
ORDER BY hour_of_day;


-----------------------------------------------------------------

SELECT
    Time,
    Class,
    ROUND(
        AVG(Class) OVER (
            ORDER BY Time
            ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW
        ),
        5
    ) AS rolling_fraud_rate
FROM transactions
ORDER BY Time
LIMIT 5000;


------------------------------------------------------------------

SELECT
    CASE
        WHEN Amount < 10 THEN 'Low (<10)'
        WHEN Amount BETWEEN 10 AND 100 THEN 'Medium (10-100)'
        ELSE 'High (>100)'
    END AS amount_bucket,
    COUNT(*) AS total_transactions,
    SUM(Class) AS fraud_transactions,
    ROUND(1.0 * SUM(Class) / COUNT(*), 5) AS fraud_rate
FROM transactions
GROUP BY amount_bucket
ORDER BY fraud_rate DESC;




-------------------------------------------------------------------

SELECT
    CAST((Time / 3600) % 24 AS INTEGER) AS hour_of_day,
    COUNT(*) AS fraud_transactions
FROM transactions
WHERE Class = 1
GROUP BY hour_of_day
ORDER BY fraud_transactions DESC
LIMIT 5;



-------------------------------------------------------------------

SELECT
    Time,
    Amount,
    Class,
    AVG(Amount) OVER (
        ORDER BY Time
        ROWS BETWEEN 500 PRECEDING AND CURRENT ROW
    ) AS rolling_avg_amount,
    SUM(Class) OVER (
        ORDER BY Time
        ROWS BETWEEN 500 PRECEDING AND CURRENT ROW
    ) AS rolling_fraud_count
FROM transactions
LIMIT 3000;