Divide Matching Rows in Table by Matching Rows in Other Table

Question

My BigQuery database models an online game. It has tables for:

• raid, an instance of the game that occurs in a channel. Example:

  +--------------------------------------+---------------+
  |                  id                  |    channel    |
  +--------------------------------------+---------------+
  | 4dc0fd49-3d13-4271-b5fb-2fc8a3bfa0c6 | #antonidas    |
  | d400e1cd-10af-4cb4-8a24-1a3f7ed994c0 | #anvilmar     |
  | 532c95b6-9398-4ae4-bd97-d9a0aed3b9cc | #antonidas    |
  | b7779b4e-5042-45ab-a356-5f2e08b51f1f | #antonidas    |
  | 41160b3b-bcd5-4f00-b3c4-972054e64cab | #antonidas    |
  +--------------------------------------+---------------+
  

• entry, the people that entered the raid. Example:

  +--------------------------------------+-----------+
  |               raid_id                |  raider   |
  +--------------------------------------+-----------+
  | 41160b3b-bcd5-4f00-b3c4-972054e64cab | Blackrock |
  | 41160b3b-bcd5-4f00-b3c4-972054e64cab | Coilfang  |
  | 4dc0fd49-3d13-4271-b5fb-2fc8a3bfa0c6 | Blackrock |
  | d400e1cd-10af-4cb4-8a24-1a3f7ed994c0 | Blackrock |
  | b7779b4e-5042-45ab-a356-5f2e08b51f1f | Blackrock |
  | b7779b4e-5042-45ab-a356-5f2e08b51f1f | Gilneas   |
  | 532c95b6-9398-4ae4-bd97-d9a0aed3b9cc | Blackrock |
  +--------------------------------------+-----------+
  

• win, the people that won the raid. Example:

  +--------------------------------------+-----------+
  |               raid_id                |  raider   |
  +--------------------------------------+-----------+
  | d400e1cd-10af-4cb4-8a24-1a3f7ed994c0 | Blackrock |
  | 4dc0fd49-3d13-4271-b5fb-2fc8a3bfa0c6 | Blackrock |
  | 41160b3b-bcd5-4f00-b3c4-972054e64cab | Coilfang  |
  +--------------------------------------+-----------+
  

I'm trying to determine the overall win rate for raids that occurred in a specific channel. The win rate is the total number of win rows divided by the total number of entry rows if the raid for the win/entry raid is channel C. Something like:

The closest I've come is by running 2 sub-select queries, but I feel there must be a better way, where I don't have to repeat myself and run essentially 3 queries total.

SELECT
  (
  SELECT
    COUNT(*)
  FROM
    win
  JOIN
    raid
  ON
    win.raid_id = raid.id
  WHERE
    raid.channel = "#antonidas") / (
  SELECT
    COUNT(*)
  FROM
    entry
  JOIN
    raid
  ON
    entry.raid_id = raid.id
  WHERE
    raid.channel = "#antonidas") AS `win_rate`

Answer 1

This query should give you your desired results. It LEFT JOINs raid to entry and win and then counts the number of distinct rows in each of those tables to determine the number of entries and wins per channel:

SELECT r.channel,
       COUNT(DISTINCT r.id) AS raids,
       COUNT(DISTINCT w.raid_id) AS wins, 
       COUNT(DISTINCT e.raid_id) AS entries,
       CAST(COUNT(DISTINCT w.raid_id) AS FLOAT) / COUNT(DISTINCT e.raid_id) AS win_rate
FROM raid r
LEFT JOIN entry e ON e.raid_id = r.id
LEFT JOIN win w ON w.raid_id = r.id
GROUP BY r.channel

Output (for your sample data):

channel     raids   wins    entries     win_rate
#antonidas  4       2       4           0.5
#anvilmar   1       1       1           1

Demo on dbfiddle

Note you can add WHERE channel = ... before the GROUP BY to restrict results to one particular channel. Also you can remove any columns you don't actually need, I put them all in to demonstrate the numbers being computed from your sample data.