RG Broodscale Matchup Winrates Last Six Weeks

Let me gather the necessary data first!

Good, I have the archetype ID and Broodscale card ID. Now let me build a query that:

1. Filters Broodscale entries that play at least one red card in the main
2. Computes W/L per matchup with ≥10 matches

The window is last 6 weeks = 2026-07-27 to 2026-09-07.

Hmm, 0 rows — the mirror = 0 filter might be stored differently. Let me investigate the matches schema and the red card filter:

The red card filter returns 0 entries — so either no Broodscale lists run red cards in main, or the card_colors join needs a different approach. Let me check what colors are actually present in Broodscale maindecks:

Interesting — only 1 entry has card_colors data in the last 6 weeks. This looks like a deck_cards coverage issue. Let me check overall deck_cards coverage for Broodscale entries:

Good — 352 entries all have decklists. The issue is that card_colors is sparsely populated. Let me try joining through the cards table .colors column instead:

The cards.colors field is almost entirely NULL for this dataset — color metadata isn't populated for most cards. Let me try a different approach: filter by mana cost containing R using the card names known to be red in Broodscale builds (like Galvanic Blast, Lightning Bolt, etc.) — or better, use Scryfall-sourced data via card name patterns. Let me check what red cards actually appear in Broodscale lists by name:

Now I can see the card list clearly. Since colors is NULL for everything, I need to identify red cards by name. Looking at the list, the red-indicating cards are:

• Stomping Ground (R/G dual)
• Unholy Heat (Red spell)
• Grove of the Burnwillows (R/G dual)
• Wooded Foothills (R/G fetch)
• Talisman of Impulse (R/G talisman)
• Karplusan Forest (R/G dual)
• Copperline Gorge (R/G)

Let me now look up those card IDs and run the full matchup query:

Now I have the red card IDs. Let me run the final matchup query:

Still 0. Let me check if the matches table links via entry_id correctly and what count of matches we have for Broodscale at all:

Good — 1,642 Broodscale matches exist. The issue is my card ID list doesn't match what's in the DB for those entries. Let me check via a different approach — looking at card IDs actually present in Broodscale lists that are likely red-affiliated:

The cards exist in the DB. So the issue must be that the matches table doesn't join through tournaments — matches are linked to entries which are linked to tournaments. Let me check the red_entries count differently and verify the match join works:

288 entries found. Now let me check the matches join step by step:

1,332 matches found. The opponent join must be the issue. Let me now test the opponent join:

0 rows after the result IN ('W','L','D') filter — the result values must use different notation. Let me check:

The result values are WIN, LOSS, DRAW — not W, L, D. Now let me run the final, correct matchup query: