# Query Implementation Plan for Killmail Analytics

## Objective
Build an interactive analytics interface where every aggregation table can be filtered by clicking rows, progressively narrowing down the dataset.

## Core Concept: Progressive Filtering
- Start with ALL killmails displayed
- Each table shows a different dimension breakdown
- Clicking ANY row in ANY table adds it as a filter
- All tables re-aggregate with the new filter set
- Filters are cumulative and removable

---

## Tables to Display

### 1. **Time Aggregations**

#### Table A: By Hour of Day
```sql
SELECT 
    kill_hour,
    count() as kill_count,
    count(DISTINCT victim_character_id) as unique_victims,
    count(DISTINCT solar_system_id) as systems_hit
FROM killmails
WHERE <active_filters>
GROUP BY kill_hour
ORDER BY kill_hour
```
**Clickable column:** `kill_hour` → adds filter `kill_hour = X`

#### Table B: By Day of Week
```sql
SELECT 
    kill_day_of_week,
    CASE kill_day_of_week
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
        WHEN 7 THEN 'Sunday'
    END as day_name,
    count() as kill_count,
    count(DISTINCT victim_character_id) as unique_victims
FROM killmails
WHERE <active_filters>
GROUP BY kill_day_of_week
ORDER BY kill_day_of_week
```
**Clickable column:** `kill_day_of_week` → adds filter `kill_day_of_week = X`

#### Table C: By Date
```sql
SELECT 
    kill_date,
    count() as kill_count,
    count(DISTINCT victim_character_id) as unique_victims,
    count(DISTINCT solar_system_id) as systems_hit
FROM killmails
WHERE <active_filters>
GROUP BY kill_date
ORDER BY kill_date DESC
```
**Clickable column:** `kill_date` → adds filter `kill_date = 'YYYY-MM-DD'`

#### Table D: By Month
```sql
SELECT 
    toYYYYMM(killmail_time) as month,
    count() as kill_count,
    count(DISTINCT victim_character_id) as unique_victims
FROM killmails
WHERE <active_filters>
GROUP BY month
ORDER BY month DESC
```
**Clickable column:** `month` → adds filter `toYYYYMM(killmail_time) = YYYYMM`

---

### 2. **Location Aggregations**

#### Table E: By Solar System
```sql
SELECT 
    solar_system_name,
    region_name,
    security,
    count() as kill_count,
    count(DISTINCT victim_character_id) as unique_victims,
    count(DISTINCT victim_ship_type_name) as ship_variety
FROM killmails
WHERE <active_filters>
GROUP BY solar_system_name, region_name, security
ORDER BY kill_count DESC
LIMIT 100
```
**Clickable columns:** 
- `solar_system_name` → adds filter `solar_system_name = 'X'`
- `region_name` → adds filter `region_name = 'X'`

#### Table F: By Region
```sql
SELECT 
    region_name,
    count() as kill_count,
    count(DISTINCT solar_system_name) as systems_with_kills,
    count(DISTINCT victim_character_id) as unique_victims
FROM killmails
WHERE <active_filters>
GROUP BY region_name
ORDER BY kill_count DESC
```
**Clickable column:** `region_name` → adds filter `region_name = 'X'`

#### Table G: By Constellation
```sql
SELECT 
    constellation_name,
    region_name,
    count() as kill_count,
    count(DISTINCT solar_system_name) as systems_in_constellation
FROM killmails
WHERE <active_filters>
GROUP BY constellation_name, region_name
ORDER BY kill_count DESC
```
**Clickable column:** `constellation_name` → adds filter `constellation_name = 'X'`

#### Table H: By Security Status
```sql
SELECT 
    CASE 
        WHEN security >= 0.5 THEN 'High Sec'
        WHEN security > 0.0 THEN 'Low Sec'
        ELSE 'Null Sec'
    END as sec_status,
    count() as kill_count,
    count(DISTINCT solar_system_name) as systems
FROM killmails
WHERE <active_filters>
GROUP BY sec_status
ORDER BY kill_count DESC
```
**Clickable column:** `sec_status` → adds filter based on security range

---

### 3. **Ship Aggregations**

#### Table I: By Victim Ship
```sql
SELECT 
    victim_ship_type_name,
    victim_ship_group_name,
    victim_ship_category_name,
    count() as kill_count,
    count(DISTINCT victim_character_id) as unique_pilots_killed
FROM killmails
WHERE <active_filters>
GROUP BY victim_ship_type_name, victim_ship_group_name, victim_ship_category_name
ORDER BY kill_count DESC
LIMIT 100
```
**Clickable columns:**
- `victim_ship_type_name` → adds filter `victim_ship_type_name = 'X'`
- `victim_ship_group_name` → adds filter `victim_ship_group_name = 'X'`
- `victim_ship_category_name` → adds filter `victim_ship_category_name = 'X'`

#### Table J: By Attacker Ship
```sql
SELECT 
    ship_type_name,
    ship_group_name,
    count() as times_used,
    count(DISTINCT killmail_id) as kills_participated,
    sum(damage_done) as total_damage,
    countIf(final_blow) as final_blows
FROM killmail_attackers
WHERE killmail_id IN (
    SELECT killmail_id FROM killmails WHERE <active_filters>
)
GROUP BY ship_type_name, ship_group_name
ORDER BY kills_participated DESC
LIMIT 100
```
**Clickable columns:**
- `ship_type_name` → adds filter on attacker ship
- `ship_group_name` → adds filter on attacker ship group

---

### 4. **Player/Entity Aggregations**

#### Table K: By Victim Character
```sql
SELECT 
    victim_character_name,
    victim_corporation_name,
    victim_alliance_name,
    count() as deaths,
    count(DISTINCT victim_ship_type_name) as ships_lost
FROM killmails
WHERE <active_filters>
GROUP BY victim_character_name, victim_corporation_name, victim_alliance_name
ORDER BY deaths DESC
LIMIT 100
```
**Clickable columns:**
- `victim_character_name` → filter by victim
- `victim_corporation_name` → filter by victim corp
- `victim_alliance_name` → filter by victim alliance

#### Table L: By Victim Corporation
```sql
SELECT 
    victim_corporation_name,
    victim_alliance_name,
    count() as losses,
    count(DISTINCT victim_character_name) as members_killed
FROM killmails
WHERE <active_filters>
GROUP BY victim_corporation_name, victim_alliance_name
ORDER BY losses DESC
LIMIT 100
```
**Clickable columns:**
- `victim_corporation_name` → filter by victim corp
- `victim_alliance_name` → filter by victim alliance

#### Table M: By Victim Alliance
```sql
SELECT 
    victim_alliance_name,
    count() as losses,
    count(DISTINCT victim_corporation_name) as corps_hit,
    count(DISTINCT victim_character_name) as members_killed
FROM killmails
WHERE <active_filters> AND victim_alliance_name != ''
GROUP BY victim_alliance_name
ORDER BY losses DESC
LIMIT 100
```
**Clickable column:** `victim_alliance_name` → filter by victim alliance

#### Table N: By Attacker Character
```sql
SELECT 
    character_name,
    corporation_name,
    alliance_name,
    count(DISTINCT killmail_id) as kills_participated,
    countIf(final_blow) as final_blows,
    sum(damage_done) as total_damage
FROM killmail_attackers
WHERE killmail_id IN (
    SELECT killmail_id FROM killmails WHERE <active_filters>
)
GROUP BY character_name, corporation_name, alliance_name
ORDER BY kills_participated DESC
LIMIT 100
```
**Clickable columns:**
- `character_name` → filter by attacker
- `corporation_name` → filter by attacker corp
- `alliance_name` → filter by attacker alliance

#### Table O: By Attacker Corporation
```sql
SELECT 
    corporation_name,
    alliance_name,
    count(DISTINCT killmail_id) as kills_participated,
    count(DISTINCT character_name) as members_involved
FROM killmail_attackers
WHERE killmail_id IN (
    SELECT killmail_id FROM killmails WHERE <active_filters>
)
GROUP BY corporation_name, alliance_name
ORDER BY kills_participated DESC
LIMIT 100
```
**Clickable columns:**
- `corporation_name` → filter by attacker corp
- `alliance_name` → filter by attacker alliance

#### Table P: By Attacker Alliance
```sql
SELECT 
    alliance_name,
    count(DISTINCT killmail_id) as kills_participated,
    count(DISTINCT corporation_name) as corps_involved,
    count(DISTINCT character_name) as members_involved
FROM killmail_attackers
WHERE killmail_id IN (
    SELECT killmail_id FROM killmails WHERE <active_filters>
) AND alliance_name != ''
GROUP BY alliance_name
ORDER BY kills_participated DESC
LIMIT 100
```
**Clickable column:** `alliance_name` → filter by attacker alliance

---

### 5. **Module/Item Aggregations** (THE COMPLEX ONES)

#### Table Q: By Module Slot Type
```sql
SELECT 
    slot_type,
    count() as items_fitted,
    count(DISTINCT killmail_id) as ships_with_slot,
    count(DISTINCT item_type_name) as module_variety
FROM killmail_items
WHERE killmail_id IN (
    SELECT killmail_id FROM killmails WHERE <active_filters>
)
GROUP BY slot_type
ORDER BY items_fitted DESC
```
**Clickable column:** `slot_type` → filter to show only items in this slot type

#### Table R: By Module (within selected slot type)
**This table only appears after slot_type is filtered**
```sql
SELECT 
    item_type_name,
    item_group_name,
    item_category_name,
    count() as times_fitted,
    count(DISTINCT killmail_id) as ships_with_module
FROM killmail_items
WHERE killmail_id IN (
    SELECT killmail_id FROM killmails WHERE <active_filters>
)
AND slot_type = <selected_slot_type>
GROUP BY item_type_name, item_group_name, item_category_name
ORDER BY times_fitted DESC
LIMIT 100
```
**Clickable columns:**
- `item_type_name` → adds filter `has this module in this slot`
- `item_group_name` → filter by module group
- `item_category_name` → filter by module category

#### Table S: Module Co-occurrence (Advanced)
**Shows what modules appear together with a selected module**
```sql
WITH selected_ships AS (
    SELECT DISTINCT killmail_id
    FROM killmail_items
    WHERE item_type_name = <selected_module>
    AND slot_type = <selected_slot_type>
    AND killmail_id IN (
        SELECT killmail_id FROM killmails WHERE <active_filters>
    )
)
SELECT 
    item_type_name,
    slot_type,
    count() as times_fitted_together,
    count() * 100.0 / (SELECT count() FROM selected_ships) as percentage
FROM killmail_items
WHERE killmail_id IN selected_ships
AND NOT (item_type_name = <selected_module> AND slot_type = <selected_slot_type>)
GROUP BY item_type_name, slot_type
ORDER BY times_fitted_together DESC
LIMIT 50
```

---

## Filter Implementation Logic

### Filter State Structure
```json
{
  "kill_hour": [13, 14],
  "solar_system_name": ["Amamake", "Dal"],
  "victim_ship_type_name": ["Algos"],
  "region_name": ["Heimatar"],
  "slot_type": ["mid"],
  "has_module": {
    "slot": "mid",
    "module_name": "Stasis Webifier II"
  }
}
```

### Building WHERE Clause
```sql
WHERE 1=1
  AND kill_hour IN (13, 14)
  AND solar_system_name IN ('Amamake', 'Dal')
  AND victim_ship_type_name = 'Algos'
  AND region_name = 'Heimatar'
  AND killmail_id IN (
      SELECT DISTINCT killmail_id 
      FROM killmail_items 
      WHERE slot_type = 'mid' 
      AND item_type_name = 'Stasis Webifier II'
  )
```

### Special Case: Module Filters
When filtering by "has module X in slot Y":
```sql
AND killmail_id IN (
    SELECT killmail_id 
    FROM killmail_items 
    WHERE slot_type = 'Y' 
    AND item_type_name = 'X'
)
```

For multiple modules in same slot (AND condition):
```sql
AND killmail_id IN (
    SELECT killmail_id
    FROM killmail_items
    WHERE slot_type = 'mid'
    AND item_type_name IN ('Module A', 'Module B')
    GROUP BY killmail_id
    HAVING count(DISTINCT item_type_name) = 2
)
```

---

## Additional Aggregatable Columns

### We CAN aggregate on:
- ✅ `kill_hour`, `kill_day_of_week`, `kill_date`, `month`
- ✅ `solar_system_name`, `constellation_name`, `region_name`, `security`
- ✅ `victim_ship_type_name`, `victim_ship_group_name`, `victim_ship_category_name`
- ✅ `victim_character_name`, `victim_corporation_name`, `victim_alliance_name`
- ✅ Attacker ships (from `killmail_attackers`)
- ✅ Attacker characters/corps/alliances (from `killmail_attackers`)
- ✅ `slot_type`, `item_type_name`, `item_group_name`, `item_category_name` (from `killmail_items`)
- ✅ Weapon types (from `killmail_attackers.weapon_type_name`)
- ✅ `final_blow` (bool - can group by attacker ships that got final blow)

### Performance Notes (ignore for now):
- These queries will be FAST on 10GB
- ClickHouse will handle millions of rows in milliseconds
- The subquery filters (`killmail_id IN (...)`) are fine - ClickHouse optimizes these well
- LIMIT 100 keeps UI snappy even with huge result sets

---

## Implementation Checklist for Clanker

1. **Query Template System**: Build WHERE clause dynamically from filter state
2. **Execute ALL aggregation queries** on every filter change
3. **Display tables** with clickable rows/columns
4. **Update filter state** on click
5. **Re-run all queries** with new filters
6. **Show active filters** with remove buttons
7. **Handle module filtering** specially (slot + type combo)

That's it. Every table. Every clickable dimension. Maximum analytics power.