Search⌘ K
AI Features
Home
Courses
The Art of PostgreSQL

Solution Review: Generating Series

Explore how to write PostgreSQL queries using the generate_series function to produce date and numeric sequences. Learn to identify leap years by checking February's length and filter out specific values from numeric series. This lesson helps you master series generation and data filtering techniques within SQL, improving your database querying skills.

We'll cover the following...

In the challenge, you were asked to perform two tasks. Here we’re going to discuss the solution to these two tasks.

Task 1

You were asked to write a query to generate a series and check which year is a leap year. Your code should output the date, year, and leap columns. The date column will take the values starting from 2000-01-01.

Solution

Click the “Run” button in the following widget to see the output.

PostgreSQL
/***************************************************/
/*****           The query to check            *****/
/*****        if a year is a leap year         *****/
/***************************************************/
select date::date,
       extract('year' from date) as year,
       extract('day' from
               (date + interval '2 month - 1 day')
              ) = 29
       as leap
  from generate_series(date '2000-01-01',
                       date '2010-01-01',
                       interval '1 year')
       as t(date);
/***************************************************/

Explanation

This section gives a detailed analysis of the solution to the first task of the challenge.

  • Lines 5–10: The select clause mentions the following columns:

    • date: This column will print the date of the first day of the year.
    • year: This
...