Create a temp table to simplify a query
The Stack Overflow data contains daily question counts through 2018-09-25 for all tags, but each tag has a different starting date in the data.
Find out how many questions had each tag on the first date for which data for the tag is available, as well as how many questions had the tag on the last day. Also, compute the difference between these two values.
To do this, first compute the minimum date for each tag.
Then use the minimum dates to select the question_count on both the first and last day. To do this, join the temp table startdates to two different copies of the stackoverflow table: one for each column - first day and last day - aliased with different names.
This exercise is part of the course
Exploratory Data Analysis in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- To clear table if it already exists
DROP TABLE IF EXISTS startdates;
-- Create temp table syntax
CREATE ___ ___ ___ AS
-- Compute the minimum date for each what?
SELECT ___,
___(date) AS mindate
FROM stackoverflow
-- What do you need to compute the min date for each tag?
___ ___ ___;
-- Look at the table you created
SELECT *
FROM ___;