tf2-schema-query.sql

-- Most expensive Engineer Unusuals with Scream Fortress VIII effects
SELECT
    particles.name || ' ' || items.item_name AS name,
    CASE
        WHEN backpack_prices.value_high IS NOT NULL 
            THEN backpack_prices.value || '-' || backpack_prices.value_high || ' ' || backpack_prices.currency
        ELSE backpack_prices.value || ' ' || backpack_prices.currency
    END AS price
FROM
    backpack_prices
JOIN particles
    ON particles.id = backpack_prices.priceindex_1
JOIN items
    ON items.defindex = backpack_prices.defindex
JOIN particle_series
    ON particle_series.id = particles.series_id
WHERE
    items.used_by_classes = ARRAY['Engineer']
AND
    backpack_prices.defindex = items.defindex
AND
    backpack_prices.quality = 5
AND
    backpack_prices.currency = 'keys'
AND 
    particles_series.name = 'Scream Fortress VIII'
ORDER BY
    (backpack_prices.value + COALESCE(backpack_prices.value_high, backpack_prices.value)) / 2 DESC
LIMIT 10;
--                     name                    |  price   
-- --------------------------------------------+----------
--  Tesla Coil Texas Ten Gallon                | 470 keys
--  Tesla Coil Texas Slim's Dome Shine         | 210 keys
--  Starstorm Insomnia Texas Slim's Dome Shine | 120 keys
--  Tesla Coil Danger                          | 120 keys
--  Tesla Coil Pardner's Pompadour             | 115 keys
--  Infernal Smoke Taunt: Rancho Relaxo        | 100 keys
--  Spectral Swirl Taunt: The Dueling Banjo    | 94 keys
--  Tesla Coil Virtual Reality Headset         | 80 keys
--  Tesla Coil Barnstormer                     | 74 keys
--  Tesla Coil Industrial Festivizer           | 69 keys



-- Skin with item name and rarity name
SELECT
    skins.name AS skin_name,
    items.defindex,
    items.item_name,
    rarities.name as grade
FROM skins
JOIN collection_items
    ON collection_items.skin_id = skins.id
JOIN items
    ON items.defindex = collection_items.item_defindex
JOIN rarities
    ON rarities.id = collection_items.rarity_id
WHERE skins.id = 5;
--    skin_name   | defindex | item_name |   grade   
-- ---------------+----------+-----------+-----------
--  Masked Mender |    15008 | Medi Gun  | Mercenary


-- Highest price for each effect
SELECT
    p.id,
    ps.name AS series,
    p.name AS particle_name,
    i.item_name AS highest_priced_item,
    CASE
        WHEN bp.value_high IS NOT NULL AND bp.value_high != bp.value
            THEN bp.value::TEXT || '-' || bp.value_high::TEXT || ' ' || bp.currency
        ELSE bp.value::TEXT || ' ' || bp.currency
    END AS highest_price
FROM particles p
JOIN particle_series ps
    ON p.series_id = ps.id
JOIN LATERAL (
    SELECT
        b.defindex,
        b.value,
        b.value_high,
        b.currency
    FROM backpack_prices b
    WHERE
        b.quality = 5
    AND 
        b.priceindex_1 = p.id
    ORDER BY
        COALESCE(b.value_high, b.value) DESC
    LIMIT 1
) bp ON TRUE
JOIN items i
    ON i.defindex = bp.defindex
WHERE
    p.series_id IS NOT NULL
ORDER BY p.id ASC;
--   id  |            series            |          particle_name           |     highest_priced_item      |  highest_price   
-- ------+------------------------------+----------------------------------+------------------------------+------------------
--     6 | Series 1                     | Green Confetti                   | Antlers                      | 800-900 keys
--     7 | Series 1                     | Purple Confetti                  | Potassium Bonnet             | 600 keys
--     8 | Series 1                     | Haunted Ghosts                   | Antlers                      | 2550 keys
--     9 | Series 1                     | Green Energy                     | Virtual Viewfinder           | 4450 keys
--    10 | Series 1                     | Purple Energy                    | Polar Pullover               | 2700 keys
--    11 | Series 1                     | Circling TF Logo                 | Antlers                      | 900 keys
--    12 | Series 1                     | Massed Flies                     | HazMat Headcase              | 600 keys
--    13 | Series 1                     | Burning Flames                   | Noh Mercy                    | 5050-7250 keys


-- Highest rank for the score type of weapons with kill_eater_score_type
-- attributes which also contain other kill_eater_score_type attribute
SELECT
    ill.name || ' ' || items.item_name AS ranked_item_name,
    kill_eater_score_types.type_name AS score_type,
    (
        SELECT
            array_agg(other_kest.type_name ORDER BY other_kest.type_name)
        FROM
            item_attributes other_ia
        JOIN
            kill_eater_score_types other_kest ON other_ia.value = other_kest.type
        JOIN
            attributes ON other_ia.attribute_defindex = attributes.defindex
        WHERE
            other_ia.item_defindex = items.defindex
        AND
            attributes.attribute_class LIKE 'kill_eater_score_type_%'
    ) AS other_score_types
FROM
    kill_eater_score_types
JOIN
    item_attributes ON item_attributes.value = kill_eater_score_types.type
JOIN
    items ON items.defindex = item_attributes.item_defindex
JOIN
    attributes ON item_attributes.attribute_defindex = attributes.defindex
JOIN
    item_levels ON item_levels.name = kill_eater_score_types.level_data
JOIN LATERAL (
    SELECT name
    FROM item_level_levels
    WHERE item_level_levels.item_level_name = item_levels.name
    ORDER BY level DESC
    LIMIT 1
) ill ON TRUE
WHERE
    attributes.attribute_class = 'kill_eater_score_type'
AND
    items.craft_material_type = 'weapon'
AND EXISTS (
    SELECT
        1
    FROM
        item_attributes other_ia
    JOIN
        kill_eater_score_types other_kest ON other_ia.value = other_kest.type
    JOIN
        attributes ON other_ia.attribute_defindex = attributes.defindex
    WHERE
        other_ia.item_defindex = items.defindex
    AND
        attributes.attribute_class LIKE 'kill_eater_score_type_%'
)
ORDER BY
    items.item_name, kill_eater_score_types.type_name;
--                ranked_item_name               |    score_type     |                    other_score_types                     
-- ----------------------------------------------+-------------------+----------------------------------------------------------
--  Hale's Own Blood Botkiller Medi Gun Mk.I     | Ubers             | {"Kill Assists"}
--  Hale's Own Blood Botkiller Wrench Mk.I       | Sentry Kills      | {Kills}
--  Hale's Own Carbonado Botkiller Medi Gun Mk.I | Ubers             | {"Kill Assists"}
--  Hale's Own Carbonado Botkiller Wrench Mk.I   | Sentry Kills      | {Kills}
--  Hale's Own Construction PDA                  | Sentry Kills      | {"Health Dispensed to Teammates","Teammates Teleported"}
--  Hale's Own Construction PDA                  | Sentry Kills      | {"Health Dispensed to Teammates","Teammates Teleported"}
--  Hale's Own Diamond Botkiller Medi Gun Mk.I   | Ubers             | {"Kill Assists"}
--  Hale's Own Diamond Botkiller Wrench Mk.I     | Sentry Kills      | {Kills}
--  Hale's Own Festive Medi Gun                  | Ubers             | {"Kill Assists"}