Complex query example

Requirement:

 You are given the table with titles of recipes from a cookbook and their page numbers. You are asked to represent how the recipes will be distributed in the book.

Produce a table consisting of three columns: left_page_number, left_title and right_title. The k-th row (counting from 0), should contain the number and the title of the page with number 2*k in the first and second columns respectively, and the title of the page with number 2*k+1 in the second column.

Each page contains at most 1 recipe. If the page does not contain a recipe, the appropriate cell should remain empty (NULL value). Page 0 (the internal side of the front cover) is guaranteed to be empty.

Data :

Desired Output :

Logic: 

1) Get page numbers from 0 to 15 in a column

2) Segregate titles based on odd/even rows

3) Reduce rows from 16 to 8

We can use Generate_series function to derive numbers from 0 to 15

But what if in future there will be more pages added to this recipe book?  In that case, instead of hardcoding page numbers from 0 to 15, we can let the subquery decide how many page numbers are needed.

Now we have 15 rows in generate_series table but the desired output has only 8 rows.

We can use left join to connect generate_series and cookbook_titles

And to make it easier to follow, we would define CTE for these 2 queries.

Now we got 2 columns that we required and the task from here is to add a 3rd column using conditional case statement. Since our desired output has only even page numbers, we can use page number odd or even as a criteria to create 3rd column.

Now we have segregated recipes in to left and right title page but we still have 16 rows. Our desired output has only 8 rows. We can use groupby concept to reduce number of rows from 16 to 8 and keep the data same in left_title and right_title columns