SQL

Examples

Most X of the Day

SELECT X, count(X)
FROM some_table
WHERE some_condition AND date_column BETWEEN '2016-04-01 00:00:00' AND '2016-04-02 00:00:00'
GROUP BY X
ORDER BY COUNT(X) DESC

Top X of the day (Historical)

SELECT date, X, MAX(count)
FROM (SELECT DATE(date_column) as date, X, COUNT(X) as count
      FROM some_table
      WHERE some_condition
      GROUP BY DATE(date_column), symbol
      ORDER BY DATE(date_column) DESC, COUNT(X) DESC
) AS whatever
GROUP BY date
ORDER BY date DESC, count DESC

Rows per date

SELECT DISTINCT(DATE(date_column)), count(date_column)
FROM some_table
GROUP BY DATE(date_column)

Distinct values per group per day + multiple filters per column + since date

SELECT DATE(date_column), grouped_column, COUNT(distinct distinct_column), count(*)
FROM table
WHERE some_column = "value"
AND (other_column LIKE "%asdf%" OR other_column LIKE "%qwer%")
AND date_column > "1984-01-01 00:00:00"
GROUP BY DATE(date_column), grouped_column
ORDER BY DATE(date_column) DESC

Find broken foreign keys (no corresponding foreign record)

SELECT * FROM
(
  SELECT thing.id AS thing_id, thing.foreign_key AS thing_foreign_key, foreign_thing.key AS foreign_thing_key
  FROM thing
  LEFT JOIN foreign_thing ON thing.foreign_key = foreign_thing.key
) AS TEMP_TABLE
WHERE TEMP_TABLE.foreign_thing_key is NULL

Ex:

SELECT * FROM
(
  SELECT book.id AS book_id, book.author_id AS book_author_id, author.id AS author_id
  FROM book
  LEFT JOIN author ON book.author_id = author.id
) AS TEMP_TABLE
WHERE TEMP_TABLE.author_id is NULL

HackerNews BigQuery hiring posts

https://news.ycombinator.com/item?id=31675750

    WITH whoishiring_threads AS (
      SELECT id FROM `bigquery-public-data.hacker_news.full`
      WHERE `by` = "whoishiring" 
      AND REGEXP_CONTAINS(title, "Ask HN: Who is hiring?")
    )

    SELECT FORMAT_TIMESTAMP("%Y-%m", `timestamp`) as year_month,
    COUNT(*) as num_toplevel_posts
    FROM `bigquery-public-data.hacker_news.full`
    WHERE parent IN (SELECT id FROM whoishiring_threads)
    GROUP BY 1
    ORDER BY 1