WITH q AS (
    SELECT
        di.name AS file_name,
        lower(di.file_extension) AS file_extension,
        di.ms365_parent_id,
        di.scan_id,
        di.ms365_drive_id,
        di.size AS file_size,
        COUNT(*) OVER (PARTITION BY di.file_extension) AS file_extension_count,
        SUM(di.size) OVER (PARTITION BY LOWER(di.file_extension)) AS file_extension_size,
        ROW_NUMBER() OVER (PARTITION BY LOWER(di.file_extension) ORDER BY di.id) AS file_extension_rownum
    FROM ms365.drive_items AS di
    JOIN ms365.drive_scans AS ds ON ds.id = di.scan_id
    --JOIN ms365.user_drives AS ud ON ud.ms365_drive_id = di.ms365_drive_id
    WHERE 1=1
      AND di.item_type = 1
      AND ds.scan_state = 1
      AND length(di.file_extension) > 0
),
p AS (
    SELECT
        q.*,
        DENSE_RANK() OVER (ORDER BY q.file_extension_size DESC) AS file_extension_size_rank
    FROM q
),
x as (
	SELECT
		d.web_url as drive_path,
		RIGHT(pp.web_url, length(pp.web_url) - length(d.web_url)) AS parent_path,
		p.file_name,
		p.file_extension,
		p.file_size,
		p.file_extension_count,
		p.file_extension_size,
		p.file_extension_size_rank,
		p.file_extension_rownum,
		CASE
			WHEN d.drive_type = 'business' THEN 'OneDrive'
			WHEN gdm.id IS NOT NULL THEN 'Teams'
			ELSE 'SharePoint'
		END AS drive_category,
		DENSE_RANK() OVER (ORDER BY p.file_extension, pp.web_url) AS file_extension_group_rownum,
		ROW_NUMBER() OVER (PARTITION BY p.file_extension ORDER BY pp.web_url, p.file_name) AS file_extension_detail_rownum
	FROM p
	JOIN ms365.drives AS d ON d.ms365_id = p.ms365_drive_id
	LEFT JOIN ms365.drive_items AS pp ON pp.scan_id = p.scan_id AND pp.ms365_id = p.ms365_parent_id
	LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id
	--WHERE p.file_extension_rank <= 10
	--  AND p.file_extension_rownum = 1
)
SELECT x.*
FROM x
WHERE x.drive_category in ('SharePoint','Teams','OneDrive')