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)
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 services
We are well known for our interview books and have 70+ e-book across Amazon and The Data Monk e-shop page . Following are best-seller combo packs and services that we are providing as of now
- YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
Link – The Data Monk Youtube Channel - Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
Link – The Data Monk website - E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions. Do check it out
Link – The Data E-shop Page - Instagram Page – It covers only Most asked Questions and concepts (100+ posts). We have 100+ most asked interview topics explained in simple terms
Link – The Data Monk Instagram page - Mock Interviews/Career Guidance/Mentorship/Resume Making
Book a slot on Top Mate
The Data Monk e-books
We know that each domain requires a different type of preparation, so we have divided our books in the same way:
1. 2200 Interview Questions to become Full Stack Analytics Professional – 2200 Most Asked Interview Questions
2.Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions
3. 30 Days Analytics Course – Most Asked Interview Questions from 30 crucial topics
You can check out all the other e-books on our e-shop page – Do not miss it
For any information related to courses or e-books, please send an email to [email protected]
