choose the right path for who is rooting your success ✔️👌

WITH RankedTraffic AS (
    SELECT 
        YEAR(record_day) AS year,
        MONTH(record_day) AS month,
        count,
        ROW_NUMBER() OVER (PARTITION BY YEAR(record_day), MONTH(record_day) ORDER BY count) AS rn_asc,
        COUNT(*) OVER (PARTITION BY YEAR(record_day), MONTH(record_day)) AS total_count
    FROM traffic
    WHERE YEAR(record_day) BETWEEN 2017 AND 2020
),
MedianTraffic AS (
    SELECT 
        year,
        month,
        CASE
            WHEN total_count % 2 = 1 THEN 
                MAX(CASE WHEN rn_asc = (total_count + 1) / 2 THEN count END)
            ELSE 
                AVG(CASE WHEN rn_asc IN (total_count / 2, (total_count / 2) + 1) THEN count END)
        END AS median
    FROM RankedTraffic
    GROUP BY year, month, total_count
)
SELECT 
    month,
    MAX(CASE WHEN year = 2017 THEN median END) AS median_2017,
    MAX(CASE WHEN year = 2018 THEN median END) AS median_2018,
    MAX(CASE WHEN year = 2019 THEN median END) AS median_2019,
    MAX(CASE WHEN year = 2020 THEN median END) AS median_2020
FROM MedianTraffic
GROUP BY month
ORDER BY month;

SELECT 
    h.id as asset_id, 
    'Hardware' as asset_type,
    h.name as asset_name,
    COALESCE(e.email, 'Unassigned') as employee_email
FROM hardware_assets h
LEFT JOIN employees e 
    ON h.employee_id = e.id
WHERE h.is_active = true

UNION ALL

SELECT 
    s.id as asset_id,
    'Software' as asset_type, 
    s.name as asset_name,
    COALESCE(e.email, 'Unassigned') as employee_email
FROM software_assets s
LEFT JOIN employees e 
    ON s.employee_id = e.id
WHERE s.is_active = true

ORDER BY asset_id ASC;

SELECT 
    protocol,
    traffic_in,
    traffic_out
FROM traffic
WHERE traffic_in > traffic_out
ORDER BY protocol ASC;