Sure, here's an example of how you might use a common table expression (CTE) to store information about game design, specifically inventory and crafting in a survival game:
WITH inventory_items AS (
    SELECT item_id, item_name, item_type
    FROM game_inventory
),
crafting_recipes AS (
    SELECT recipe_id, item_id, required_items, output_item_id
    FROM game_crafting_recipes
)
SELECT i1.item_name AS input_item, i2.item_name AS output_item
FROM inventory_items i1
JOIN crafting_recipes cr ON i1.item_id = cr.item_id
JOIN inventory_items i2 ON cr.output_item_id = i2.item_id
In this example, two CTEs are defined: inventory_items and crafting_recipes. The inventory_items CTE retrieves information about all items in the game's inventory, such as item name and type. The crafting_recipes CTE retrieves information about crafting recipes, including the recipe ID, required items, and the resulting output item.
The final query uses these CTEs to join the inventory_items and crafting_recipes tables, allowing you to see which input items can be used to craft which output items.
This CTE can be useful when you want to reuse the same subquery multiple times within your main query, making it easier to read and maintain the code.
spam fails_task lang_mismatch pii not_appropriate hate_speech sexual_content quality toxicity humor helpfulness creativity violence