Difference between 2 minimum dates

Question

i have a set of data in Big query that has visits by customer by each date ( separate rows) . I want to find the days gap between 1st date of purchase and 2nd one . It looks like this

Member card number saledate
AA687980 11/03/2017
AA890785 21/05/2016
AA687980 30/05/2017
BBY679088 04/04/2018
AA687980 29/05/2018

I need the following result
AA687980 80 days ( 30/05/2017 -11/03/2017)

I used the following query , but there is an error . I am using Big query

SELECT TB1.membercardnumber
,TB1.saledate
,TB1.nextdate
,date_diff(TB1.saledate, TB1.nextdate, day)
FROM (SELECT (TB2.membercardnumber
, TB2.saledate
(SELECT MIN(TB2.saledate)
FROM Table TB2)
WHERE TB2.membercardnumber =TB1.membercardnumber AND TB2.saledate >TB1.saledate) AS Nextdate
FROM Table TB1) AS T

You can use the DATE_DIFF function [https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#date_diff ] that returns the difference between two dates. Here [https://stackoverflow.com/questions/9994862/date-difference-between-consecutive-rows] you can see one example that uses multiples rows and calculates the difference between two dates. Let me know if this helps you. — gr7, May 14 '18 at 15:28
I am getting a bit lost in the referencing of tables T1 and T2 refers to the same table ? — Mansi, May 15 '18 at 00:15
I am updating my question with the query used . Can you please find the mistake — Mansi, May 15 '18 at 00:23

score 1 · Accepted Answer · answered May 15 '18 at 01:21

Below is for BigQuery Standard SQL

#standardSQL
SELECT
  card, 
  DATE_DIFF(
    PARSE_DATE('%d/%m/%Y', SPLIT(str, ' - ')[SAFE_OFFSET(1)]),
    PARSE_DATE('%d/%m/%Y', SPLIT(str, ' - ')[SAFE_OFFSET(0)]),
    DAY) days,
  str
FROM (
  SELECT
    card, STRING_AGG(saledate, ' - ' ORDER BY PARSE_DATE('%d/%m/%Y', saledate) LIMIT 2) str
  FROM `project.dataset.your_table`
  GROUP BY card
)
WHERE NOT SPLIT(str, ' - ')[SAFE_OFFSET(1)] IS NULL

You can test / play with it using your dummy data as below

#standardSQL
WITH `project.dataset.your_table` AS (
  SELECT 'AA687980' card, '11/03/2017' saledate UNION ALL
  SELECT 'AA890785', '21/05/2016' UNION ALL
  SELECT 'AA687980', '30/05/2017' UNION ALL
  SELECT 'BBY679088', '04/04/2018' UNION ALL
  SELECT 'AA687980', '29/05/2018' 
)
SELECT
  card, 
  DATE_DIFF(
    PARSE_DATE('%d/%m/%Y', SPLIT(str, ' - ')[SAFE_OFFSET(1)]),
    PARSE_DATE('%d/%m/%Y', SPLIT(str, ' - ')[SAFE_OFFSET(0)]),
    DAY) days,
  str
FROM (
  SELECT
    card, STRING_AGG(saledate, ' - ' ORDER BY PARSE_DATE('%d/%m/%Y', saledate) LIMIT 2) str
  FROM `project.dataset.your_table`
  GROUP BY card
)
WHERE NOT SPLIT(str, ' - ')[SAFE_OFFSET(1)] IS NULL

result will be

Row card        days    str  
1   AA687980    80      11/03/2017 - 30/05/2017

Difference between 2 minimum dates

1 Answers1