Unnest and Sequence in Presto SQL
Unnest Sequence in Presto SQL helps you to create a sequence of numbers, dates, etc. as a table and then to use that table. Unnest and sequence are two different functions but when used in a combination it can be very useful
Where to use Unnest and Sequence in Presto?
Suppose, you have a table where you store restaurant id, revenue, and date. Now, there are chances that some restaurants are not functional on a particular date, but in your query, you need to get the total number of restaurants functional(not operational) on a particular day.
This is a standard real-life problem, basically, you need to have a calendar table in your database where you have only dates. With this date column, you will do a cross join on the restaurant table. Now, you will have all the dates for each restaurant. All, you need to do now is to take up the count distinct of the hotel_ids for a particular day.
Sample SQL code to create a sequence
from unnest(sequence(date ‘2020-01-01’, date ‘2022-03-31’)) t(x)
The above command will create a column x with values starting from 2020 till 31st March 2022. Actually, the sequence will create the data in one row, and unnest will explode that row.
sequence (start, stop, step)
In the sequence function, you can also add steps like below
from unnest(sequence(date ‘2020-01-01’, date ‘2022-03-31’, Interval ‘2’ day)) t(x)
The above code will create the column x for dates separated by 2 days.
SELECT x FROM UNNEST(sequence(200, 120, -3)) t(x)
Sample code to plug it into your report
from mytable t
cross join unnest(sequence(date(t.start_dt), date(t.end_dt))) as s(dt)
Now, here you took the column id and did a cross join on the sequence date column. This will result in creating two columns i.e. id and all the dates
This is a simple concept but is asked as a part of a larger problem in an interview.
The Data Monk Interview Books – Don’t Miss
Now we are also available on our website where you can directly download the PDF of the topic you are interested in. At Amazon, each book costs ~299, on our website we have put it at a 60-80% discount. There are ~4000 solved interview questions prepared for you.
10 e-book bundle with 1400 interview questions spread across SQL, Python, Statistics, Case Studies, and Machine Learning Algorithms – Ideal for 0-3 years experienced candidates
23 E-book with ~2000 interview questions spread across AWS, SQL, Python, 10+ ML algorithms, MS Excel, and Case Studies – Complete Package for someone between 0 to 8 years of experience (The above 10 e-book bundle has a completely different set of e-books)
12 E-books for 12 Machine Learning algorithms with 1000+ interview questions – For those candidates who want to include any Machine Learning Algorithm in their resume and to learn/revise the important concepts. These 12 e-books are a part of the 23 e-book package
Important Resources to crack interviews (Mostly Free)
There are a few things that might be very useful for your preparation
The Data Monk Youtube channel – Here you will get only those videos that are asked in interviews for Data Analysts, Data Scientists, Machine Learning Engineers, Business Intelligence Engineers, Analytics Manager, etc.
Go through the watchlist which makes you uncomfortable:-
All the list of 200 videos
Complete Python Playlist for Data Science
Company-wise Data Science Interview Questions – Must Watch
All important Machine Learning Algorithm with code in Python
Complete Python Numpy Playlist
Complete Python Pandas Playlist
SQL Complete Playlist
Case Study and Guesstimates Complete Playlist
Complete Playlist of Statistics