BQ puzzles

bq.sql

-- get hours on a project, 0 is no hours
SELECT
  name,
  ifnull(SUM(w.hours),
    0) AS hours
FROM (
  SELECT
    DISTINCT(name),
    id,
    0 AS hours
  FROM
    `bq-puzzles.tutorial.users`
  WHERE
    name IS NOT NULL) AS u
LEFT JOIN (
  SELECT
    user,
    SUM(hours) AS hours
  FROM
    `bq-puzzles.tutorial.weeklygoals`
  WHERE
    project = 'blogideas'
  GROUP BY
    user) AS w
ON
  u.id=w.user
GROUP BY
  name
ORDER BY
  hours

-- How many members in team
WITH
  teams AS (
  SELECT
    team,
    COUNT(id) AS members_in_team
  FROM
    `bq-puzzles.tutorial.users`
  GROUP BY
    team)
SELECT
  id,
  name,
  users.team,
  members_in_team
FROM
  `bq-puzzles.tutorial.users` users
LEFT JOIN
  teams
ON
  users.team = teams.team

-- How many members in team w/ analytic functions
SELECT
  *,
  COUNT(id) OVER(PARTITION BY team) AS members_in_team,
  COUNT(id) OVER() AS all_people
FROM
  `bq-puzzles.tutorial.users`

-- Weekly goal hours per user. Weeks start on a monday
SELECT
  user,
  DATE_TRUNC(CAST(timestamp AS DATE), WEEK(MONDAY)) week,
  SUM(hours) AS hours
FROM
  `bq-puzzles.tutorial.weeklygoals`
GROUP BY
  user,
  week
ORDER BY
  user,
  week

-- Weekly goal hours per user w/ running goals
SELECT
  user,
  week,
  hours,
  SUM(hours) OVER(PARTITION BY user ORDER BY week ROWS BETWEEN 100 PRECEDING AND CURRENT ROW) as running
FROM (
  SELECT
    user,
    DATE_TRUNC(CAST(timestamp AS DATE), WEEK(MONDAY)) week,
    SUM(hours) AS hours
  FROM
    `bq-puzzles.tutorial.weeklygoals`
  GROUP BY
    user,
    week)
ORDER BY
    user,
    week

-- Total hours, rank, ratio
SELECT
  user,
  total,
  rank,
  ROUND(total / SUM(total) OVER() * 100, 2) as ratio
FROM (
  SELECT
    user,
    SUM(hours) AS total,
    RANK() OVER(ORDER BY SUM(hours) DESC) AS rank
  FROM
    `bq-puzzles
.tutorial.weeklygoals`
  GROUP BY
    user
  ORDER BY
    total DESC)