Register Now

Login

Lost Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Login

Register Now

It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways

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

select x
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 command
sequence (start, stop, step)
In the sequence function, you can also add steps like below

select x
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)

Unnest Sequence in Presto SQL

Sample code to plug it into your report

select t.id,s.dt
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

Individual 50+ e-books on separate topics

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

About TheDataMonkGrand Master

I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)

Follow Me