CODEWITHSUNDEEP
sqlgoogle-bigqueryforeign-keysrelational-database
user15560640
user15560640

Reputation: 3

Showing Two Fields With Different Timeline in the Same Date Structure

In the project I am currently working on in my company, I would like to show sales related KPIs together with Customer Score metric on SQL / Tableau / BigQuery

The primary key is order id in both tables. However, order date and the date we measure Customer Score may be different. For example the the sales information for an order that is released in Feb 2020 will be aggregated in Feb 2020, however if the customer survey is made in March 2020, the Customer Score metric must be aggregated in March 2020. And what I would like to achieve in the relational database is as follows:

Sales:

Order ID Order Date(m/d/yyyy) Sales ($)
1000 1/1/2021 1000
1001 2/1/2021 2000
1002 3/1/2021 1500
1003 4/1/2021 1700
1004 5/1/2021 1800
1005 6/1/2021 900
1006 7/1/2021 1600
1007 8/1/2021 1900

Customer Score Table:

Order ID Customer Survey Date(m/d/yyyy) Customer Score
1000 3/1/2021 8
1001 3/1/2021 7
1002 4/1/2021 3
1003 6/1/2021 6
1004 6/1/2021 5
1005 7/1/2021 3
1006 9/1/2021 1
1007 8/1/2021 7

Expected Output:

KPI Jan-21 Feb-21 Mar-21 Apr-21 May-21 June-21 July-21 Aug-21 Sep-21
Sales($) 1000 2000 1500 1700 1800 900 1600 1900
AVG Customer Score 7.5 3 5.5 3 7 1

I couldn't find a way to do this, because order date and survey date may/may not be the same.

For sample data and expected output, click here.

Upvotes: 0

Views: 110

Answers (1)

Leigha Jarett
Leigha Jarett

Reputation: 188

I think what you want to do is aggregate your results to the month (KPI) first before joining, as opposed to joining on the ORDER_ID

For example:

with order_month as (
  select date_trunc(order_date, MONTH) as KPI, sum(sales) as sales
  from `testing.sales`
  group by 1
  ),
  customer_score_month as (
  select date_trunc(customer_survey_date, MONTH) as KPI, avg(customer_score) as avg_customer_score
  from `testing.customer_score`
  group by 1
  )
  select coalesce(order_month.KPI,customer_score_month.KPI) as KPI, sales, avg_customer_score 
  from order_month
  full outer join customer_score_month
  on order_month.KPI = customer_score_month.KPI
  order by 1 asc

Here, we aggregate the total sales for each month based on the order date, then we aggregate the average customer score for each month based on the date the score was submitted. Now we can join these two on the month value.

This results in a table like this:

KPI sales avg_customer_score
2021-01-01 1000 null
2021-02-01 2000 null
2021-03-01 1500 7.5
2021-04-01 1700 3.0
2021-05-01 1800 null
2021-06-01 900 5.5
2021-07-01 1600 3.0
2021-08-01 1900 7.0
2021-09-01 null 1.0

You can pivot the results of this table in Tableau, or leverage a case statement to pull out each month into its own column - I can elaborate more if that will be helpful

Upvotes: 0

Related Questions