1. Learn
    2. /
  2. Courses
    3. /
  3. Exploratory Data Analysis in SQL
Connected

Exercise

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.

Instructions 1/2

undefined XP
    1
    2
  • First, create a temporary table called startdates with each tag and the min() date for the tag in stackoverflow.