Advanced SQL: Aggregates, Subqueries & Window Functions

SELECT
    COUNT(*)               AS total_scans,
    COUNT(DISTINCT host)   AS unique_hosts,
    MIN(created_at)        AS first_scan,
    MAX(created_at)        AS last_scan
FROM scans;

-- Scan count per user
SELECT
    u.username,
    COUNT(s.id)       AS scan_count,
    COUNT(DISTINCT s.host) AS unique_hosts
FROM users u
LEFT JOIN scans s ON s.user_id = u.id
GROUP BY u.id, u.username
ORDER BY scan_count DESC;

-- Open ports per service
SELECT
    service,
    COUNT(*) AS count,
    AVG(port) AS avg_port
FROM ports
WHERE status = 'open'
GROUP BY service;

-- Users with more than 5 scans
SELECT user_id, COUNT(*) AS cnt
FROM scans
GROUP BY user_id
HAVING cnt > 5;

-- Services with more than 3 open ports
SELECT service, COUNT(*) AS open_count
FROM ports
WHERE status = 'open'
GROUP BY service
HAVING open_count > 3
ORDER BY open_count DESC;

-- Users who have NEVER done a scan
SELECT u.username
FROM users u
LEFT JOIN scans s ON s.user_id = u.id
WHERE s.id IS NULL;

-- Hosts with no open ports
SELECT h.ip
FROM hosts h
LEFT JOIN ports p ON p.host_id = h.id AND p.status = 'open'
WHERE p.id IS NULL;

-- Non-correlated: inner query runs once
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM scans WHERE status = 'complete'
);

-- Correlated: inner query references outer
SELECT u.username
FROM users u
WHERE EXISTS (
    SELECT 1 FROM scans s
    WHERE s.user_id = u.id
      AND s.created_at > DATE('now', '-7 days')
);

-- Scalar subquery
SELECT
    username,
    (SELECT COUNT(*) FROM scans s WHERE s.user_id = u.id) AS scan_count
FROM users u;

-- WITH makes complex queries readable
WITH recent_scans AS (
    SELECT *
    FROM scans
    WHERE created_at > DATE('now', '-30 days')
),
user_stats AS (
    SELECT
        user_id,
        COUNT(*) AS scan_count,
        COUNT(DISTINCT host) AS unique_hosts
    FROM recent_scans
    GROUP BY user_id
)
SELECT
    u.username,
    us.scan_count,
    us.unique_hosts
FROM users u
JOIN user_stats us ON us.user_id = u.id
WHERE us.scan_count > 2
ORDER BY us.scan_count DESC;

-- ROW_NUMBER: rank within partition
SELECT
    username,
    scan_count,
    ROW_NUMBER() OVER (ORDER BY scan_count DESC) AS rank
FROM user_stats;

-- RANK: same rank for ties, gaps after
SELECT
    host,
    open_ports,
    RANK() OVER (ORDER BY open_ports DESC) AS danger_rank
FROM host_stats;

-- Running total
SELECT
    created_at,
    scan_count,
    SUM(scan_count) OVER (ORDER BY created_at) AS running_total
FROM daily_scans;

-- LAG/LEAD: access previous/next row
SELECT
    created_at,
    scan_count,
    LAG(scan_count, 1) OVER (ORDER BY created_at) AS prev_day,
    scan_count - LAG(scan_count, 1) OVER (ORDER BY created_at) AS delta
FROM daily_scans;

-- Conditional column
SELECT
    host,
    status,
    CASE
        WHEN status = 'open'    THEN 'VULNERABLE'
        WHEN status = 'filtered' THEN 'UNKNOWN'
        ELSE 'SAFE'
    END AS risk_level
FROM ports;

-- CASE in aggregate
SELECT
    COUNT(CASE WHEN status = 'open' THEN 1 END)    AS open_ports,
    COUNT(CASE WHEN status = 'closed' THEN 1 END)  AS closed_ports
FROM ports;

-- String
SELECT UPPER(username), LOWER(email), LENGTH(username) FROM users;
SELECT SUBSTR(email, 1, INSTR(email, '@') - 1) AS local_part FROM users;
SELECT REPLACE(host, '10.', '192.168.') FROM scans;

-- Date (SQLite)
SELECT DATE('now');                         -- 2025-01-15
SELECT DATE('now', '-7 days');             -- 7 days ago
SELECT STRFTIME('%Y-%m', created_at)       -- month bucket
FROM scans;

-- Group by month
SELECT
    STRFTIME('%Y-%m', created_at) AS month,
    COUNT(*) AS scans
FROM scans
GROUP BY month
ORDER BY month;