2 ms·
So that appears to be a fun edge case with that quick SQL query! The spike was in March which was the start of the COVID-19 pandemic in the United States/shelt
by minimaxir 4y ago
So that appears to be a fun edge case with that quick SQL query!
The spike was in March which was the start of the COVID-19 pandemic in the United States/shelter-in-place, which was a time of odd behavior and so a spike wouldn't be too weird.
It turns out that dang posted a special whoishiring soon after which was massively popular: https://news.ycombinator.com/item?id=22665398 https://news.ycombinator.com/item?id=22665398
I thought I filtered out nonstandard threads in the query:
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?")
)
...but that filter is a regex, and in a regex the `?` is a modifier character and not a literal. So the query will combine the counts of both the top-level comments of that thread and the original one.
Data science is fun like that, and surprisingly not the first time I've made that particular query mistake.
- boulos 4y agoThis is part of why I prefer to use LIKE directly when the thing I'm doing isn't actually a regexp. Then again, it's equally easy to screw up the %, but I feel it's maybe more visible (due to the less common characters). [My primary reason is clarity for the reader, so they don't have to attempt to parse the potential regexp]. More pernicious than ? is . though. Not that it matters in your case, but a lot of matches really can be "oops, a one character substitution totally matches, too".
- minimaxir 4y agoYou have more experience in this area, but REGEXP_CONTAINS() seems faster than LIKE for bigger datasets IMO. Although in my work I tend to use REGEXP_CONTAINS() as an efficient multifilter for different inputs, which speeds things up too.
- boulos 4y agoHmm. I'm totally going to do some benchmarking tomorrow...