Get startedGet started for free

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

View Course

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 ___;
Edit and Run Code