How `With Clause` statements in SQL simplify my job.

By : Pitchstar Chernenko, BI Analyst at tiket.com, a citizen journalist

tiket.com has a dedicated team to manage their own data. tiket.com Data Team was started approximately 2 years ago. Now they called themselves as Data Rangers. Yep, that’s us.

But,

Why would they have a dedicated team only to manage the data?

Why don’t they have more business people to expand and grow their business fast?

Here’s why:

Welcome to the new era where data is the new oil.

Data is the new oil. That’s what these people were talking about. Believe it or not, I think this is the right for you to consider building your own Big Data Infrastructure and your dedicated data team.

When we talk about data especially Big Data, we need a very very very large environment to make sure the data supplied and processed correctly yet sophistically fast. That’s why we Data Rangers have 3 teams. They are:

• Data Engineer
• Data scientist
• Business Intelligence Analyst

Those teams are working collaboratively and responsively to achieve and deliver the maximum result for our users.

Here’s a picture of us, by the way. Do you spot me?

I am working in Business Intelligence team. In most cases, my job is to develop a customized report based on what user needs. Usually, I did a lot of improvements by connecting the required data from other sources such as Google Analytics, Salesforce, even scrape external data from the Internet.

Our data is stored in Google BigQuery, no wonder we have to do a lot of queries every day, and it turned out that With Clausestatement simplifies my job greatly.

Write more stories by using `With Clause` statement.

As a blogger who writes a lot of articles about Personal Finance, I love to write. I simply love to write. Before I write, I will make my own draft. I will prepare the main idea and let my imaginations explore them more. The same thing happens when I build my Query by using With Clause statement.

The following query is a sample of working with With Clause statement. Of course, I wouldn’t recommend you to use `select *` due to many reasons.

with book as(
Select *
from master_book
where title="A guide to personal finance"
),select chapter as(
select *
from pages
where chapter_title "how to manage your liabilities effectively"
)select *
from book
inner join on pages.book_id=book.book_id

Why did i write “book” at the top of my query, you asked?

Because I have a story in my mind. When I want to get certain pages, I need to know what book it is. Therefore, I am going to need master_book before reaching out pages . I have to know what the title is, so I put where clause to define the title. Also, I have to know what chapter that I looking for, so I put another where clause to define the chapter in pages subquery.

Of course, I can add more subquery related to pages below pages subquery, and so on. This way, I can tell you that my queries have a story to tell.

`With Clause` help my peer to understand and improve my SQL

I personally think that With Clause is more readable and clean.

I asked many peers about the following queries:

Hey bro, which one is the most readable and you understand?

SQL 1:

with book as(
Select *
from master_book
where title="A guide to personal finance"
),select chapter as(
select *
from pages
where chapter_title "how to manage your liabilities effectively"
)select *
from book
inner join chapter on pages.book_id=book.book_id

SQL 2:

select *
from (select *
from book subbookinner join(
select *
from pages
where chapter_title "how to manage your liabilities effectively)subpages
on subbook.book_id=subpages.book_id)
where title="A guide to personal finance"

My friends prefer to choose SQL 1 instead of 2. We can add more columns easily by using SQL 1. We need to know which table that the desired columns are located, and add them into subqueries. At the end of the page, insert the new columns.

I would credit this article for anyone who creates a `With Clause` statement. I can create any SQL that readable, understandable, and ready to use by others.

“Hey, why don’t you start build a query by using With Clause statement from now?”