How can I calculate difference between dates in the same column but different categories?

Question

I'm sure someone will point out that this is a basic reshaping problem, but I guess I'm poor at Googling, so I'm offering my question up to the gods of Stack Overflow.

My problem is that I want to calculate the number of days between two dates (of Rank 1 and 2) that come from the same column but have different values in another column (Rank). Here's an example of the dataset schema:

User   Date        Rank

Bob    2016-12-01    3

Bob    2016-12-07    2

Bob    2016-12-10    1

What I would like is this:

User   Date1        Date2        DaysBetween

Bob    2016-12-07   2016-12-10        3 

Ideally, I wanted to do this in Tableau, but SQL/BigQuery is OK, too 

Answer 1

• You can try the mentioned code below:

WITH YourTable AS (

  SELECT 'Bob' AS User, DATE '2016-12-01' AS Date, 3 AS Rank UNION ALL

  SELECT 'Bob' AS User, DATE '2016-12-07' AS Date, 2 AS Rank UNION ALL

  SELECT 'Bob' AS User, DATE '2016-12-10' AS Date, 1 AS Rank 

)

SELECT 

  User, 

  MAX(CASE WHEN Rank = 2 THEN Date END) AS Date1,

  MAX(CASE WHEN Rank = 1 THEN Date END) AS Date2,

  DATE_DIFF(MAX(CASE WHEN Rank = 1 THEN Date END), 

            MAX(CASE WHEN Rank = 2 THEN Date END), DAY) AS DaysBetween

FROM YourTable

GROUP BY User

• Refer to this link for Standard SQL as this is in the big query :https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql