Search code examples
google-bigquerystackexchange-apistackexchange

How to retrieve number of tags at a certain time in the past

I can see here how to get number of tags through StackExchange API. Is it possible to get number of tags at a certain time in the past?

How can I run https://api.stackexchange.com/2.2/tags?order=desc&sort=popular&inname=java&site=stackoverflow with BigQuery?

Solution

  • One of potentially many options - for BigQuery Standard SQL 

    #standardSQL
SELECT tag, COUNT(1) AS popular
FROM `bigquery-public-data.stackoverflow.stackoverflow_posts`, 
    UNNEST(SPLIT(tags, '|')) AS tag
WHERE DATE(creation_date) 
    BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR) 
    AND DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR)
GROUP BY tag
HAVING tag LIKE '%java%'
ORDER BY popular DESC
-- LIMIT 100