Big Query (SQL): Date Problems or Situations that are Hard to Analyze and Takes Time To Crack

Big Query (SQL)

   Being a Business Analyst in a famous E-commerce in the Philippines, we used Google Big Query to extract some data from an E-commerce platform. Big Query is a Structured Query Language that manipulates raw data in a summarized and desired information.

    As a beginner user,  it is very hard to understand since it was not taught in college. However, I am privileged and happy because Big Query is owned by Google Company, hence, it is very expensive, so, this is once in a lifetime opportunity to grab on and a ladder to your career.

    Though it is relatively difficult for me, I want to share with you two situations or problems that may you have encountered in Big Query. In the meantime, I will give only a broader hint in analyzing the problem but later in the next post, I will show you my way to solve my problem. 

Getting a Monthly Date Range

    Big Query has a lot of Date Functions. These are the following:

  • CURRENT_DATE
  • EXTRACT
  • DATE
  • DATE_ADD
  • DATE_SUB
  • DATE_DIFF
  • DATE_TRUNC
  • DATE_FROM_UNIX_DATE
  • FORMAT_DATE
  • LAST_DAY
  • PARSE_DATE
  • UNIX_DATE
For more information in each date functions, read it here.

     This is the problem I have encountered. Given those dates below, you should make a date range. A date range starts from 29th of the month and ends with 28th of the next month.  Assuming the table name is Table_Range.

    The result should be like this. Hint: Use some of those date functions that are listed above.

Delivery_Due_Date

Starting Date

Ending Date

03-13-2020

02-29-2020

03-28-2021

06-30-2020

06-29-2020

07-28-2021

01-01-2021

12-29-2020

01-28-2021

11-11-2021

10-28-2021

11-29-2021


I also asked the Stackoverflow Community page and they have also their suggested answer. You can also check my suggested query here.

A Decimal String Style of Datestamp from an Airtable to a Correct and True DateStamp in a Big Query.

    Airtable is an online database tool that organizes projects, collabs data, and manages project schedules. The archives that are produced in this online tool can be uploaded as raw data in Big Query. 

    In Airtable the DateTime Data is correct but if you upload it in the Big Query, it will transform into a Decimal String Style of Numbers.  See the table below:

Delivery_Due_Date

True Date

44493.61666667

2021-10-24T14:48:00

44522.775

2021-11-22T18:36:00

44493.67916666667

2021-10-24T16:18:00

44522.739583333336

2021-11-22T17:45:00

    This is the problem I have encountered. Assuming the table name is Airtable.  Can you give a suggested query to convert it into the correct and true timestamp? Hint: I used the split function and offset function to solve the second column.

I also asked the Stackoverflow Community page and they have also their suggested answer. You can also check my suggested query here.

    As of now, these are the problems that I have encountered in my career.

  If you want to share your suggested ways to solve it, I am very lovely and thankful to see and read on it. Just comment on this post for your suggested queries. 

Post a Comment

1 Comments