83 lines
2.8 KiB
SQL
83 lines
2.8 KiB
SQL
-- ============================================================
|
||
-- CS2 Skin Tracker — catalog audit (read-only)
|
||
-- Run against the skintracker database as any role with SELECT
|
||
-- (e.g. skintracker_readonly). Pure SELECTs, safe to re-run.
|
||
--
|
||
-- Purpose: sanity-check the weapon/glove skin catalog for the
|
||
-- gaps that break tradeup math and pricing joins.
|
||
-- ============================================================
|
||
|
||
SET search_path = skintracker;
|
||
|
||
-- 1. Totals — quick scale check.
|
||
SELECT 'skins' AS table, count(*) FROM skins
|
||
UNION ALL SELECT 'weapons', count(*) FROM weapons
|
||
UNION ALL SELECT 'collections', count(*) FROM collections
|
||
UNION ALL SELECT 'skin_collections', count(*) FROM skin_collections
|
||
UNION ALL SELECT 'skin_conditions', count(*) FROM skin_conditions;
|
||
|
||
-- 2. Orphan skins: belong to NO collection/container.
|
||
-- Expected: only "Howl" (Contraband, removed from its collection).
|
||
SELECT s.id, s.slug, s.name, s.rarity
|
||
FROM skins s
|
||
LEFT JOIN skin_collections sc ON sc.skins_id = s.id
|
||
WHERE sc.skins_id IS NULL
|
||
ORDER BY s.name;
|
||
|
||
-- 3. Missing float bounds.
|
||
-- Expected: only Vanilla (default) knives, which have no wear range.
|
||
SELECT s.id, s.slug, s.name, s.float_min, s.float_max
|
||
FROM skins s
|
||
WHERE s.float_min IS NULL OR s.float_max IS NULL
|
||
ORDER BY s.name;
|
||
|
||
-- 4. Rarity hygiene: any nulls/blanks, and the distribution.
|
||
SELECT coalesce(nullif(rarity, ''), '<empty/null>') AS rarity, count(*)
|
||
FROM skins
|
||
GROUP BY 1
|
||
ORDER BY 2 DESC;
|
||
|
||
-- 5. Inverted or out-of-range float bounds (should return 0 rows).
|
||
SELECT id, slug, name, float_min, float_max
|
||
FROM skins
|
||
WHERE float_min > float_max OR float_min < 0 OR float_max > 1;
|
||
|
||
-- 6. Duplicate slugs (unique index should keep this empty).
|
||
SELECT slug, count(*)
|
||
FROM skins
|
||
GROUP BY slug
|
||
HAVING count(*) > 1;
|
||
|
||
-- 7. Collection type split (expect only Collection / Container).
|
||
SELECT coalesce(type, '<null>') AS type, count(*)
|
||
FROM collections
|
||
GROUP BY 1
|
||
ORDER BY 2 DESC;
|
||
|
||
-- 8. Skins with no weapon link (should return 0 rows).
|
||
SELECT s.id, s.slug
|
||
FROM skins s
|
||
LEFT JOIN weapons w ON w.id = s.weapon_id
|
||
WHERE w.id IS NULL;
|
||
|
||
-- 9. Skin count per weapon type — confirms only weapon/glove items.
|
||
-- Note: Zeus x27 (type "Equipment") skins ARE valid catalog/tradeup items.
|
||
SELECT w.type, count(s.id) AS skins
|
||
FROM weapons w
|
||
LEFT JOIN skins s ON s.weapon_id = w.id
|
||
GROUP BY w.type
|
||
ORDER BY skins DESC;
|
||
|
||
-- 10. StatTrak / Souvenir availability.
|
||
SELECT
|
||
sum(CASE WHEN stat_trak_available THEN 1 ELSE 0 END) AS stattrak,
|
||
sum(CASE WHEN souvenir_available THEN 1 ELSE 0 END) AS souvenir,
|
||
count(*) AS total
|
||
FROM skins;
|
||
|
||
-- 11. true_float split (capped range vs full 0.0–1.0; null = unknown bounds).
|
||
SELECT coalesce(true_float::text, '<null>') AS true_float, count(*)
|
||
FROM skins
|
||
GROUP BY 1
|
||
ORDER BY 2 DESC;
|