I am trying create a new table in BigQuery including two columns with the newest and oldest date.

Source Table:

Result:

And this is my Query:

        SELECT
      concatenatedString,
      MIN(FirstLastDate) AS KampagneStart,
      MAX(FirstLastDate) AS KampagneEnd
    FROM (
      SELECT
        MAX(date) AS FirstLastDate,
        CONCAT(ifnull(Campaign_Name,
            ''),'|', ifnull(Campaign_Category,
            ''),'|',ifnull(Product_Category,
            ''),'|',ifnull(Powerbrand_Powerline,
            ''),'|',ifnull(Channel,
            '')) AS concatenatedString
      FROM
        `bigquery-project.ETL_Ready.Union`
      GROUP BY
        concatenatedString
      UNION ALL
      SELECT
        MIN(date) AS FirstLastDate,
        CONCAT(ifnull(Campaign_Name,
            ''),'|', ifnull(Campaign_Category,
            ''),'|',ifnull(Product_Category,
            ''),'|',ifnull(Powerbrand_Powerline,
            ''),'|',ifnull(Channel,
            '')) AS concatenatedString
      FROM
        `bigquery-project.ETL_Ready.Union`
      GROUP BY
        concatenatedString )
    GROUP BY
      concatenatedString

The problem is that in the source table I get the values of the concatenatedString in separate columns and sometimes I have agregate another values. This is why this query is not very flexible for me.

Does anyone knows a efficient way to do achieve the same result without using UNION ALL?

CodePudding user response：

Consider below approach

select concatenatedString, 
  min(date) as Oldest_Date,
  max(date) as Newest_Date
from your_table
group by concatenatedString           

if applied to sample data in your question - output is

Another option would be

select * from your_table
pivot (min(date) as Oldest, max(date) as Newest for 'Date' in ('Date'))    

with same output

P.S. below is CTE I used for "testing"

with your_table as (
  select '2019-08-27' date, 'Housesystems-ActivationHouses-Multiple-Display & Video' concatenatedString union all
  select '2019-09-03', 'Housesystems-ActivationHouses-Multiple-Display & Video' union all
  select '2019-11-28', 'Housesystems-ActivationHouses-Multiple-Display & Video' union all
  select '2019-09-01', 'Housesystems-ActivationHouses-Multiple-Paid Social' union all
  select '2020-09-11', 'Housesystems-ActivationHouses-Multiple-Paid Social' union all
  select '2021-07-06', 'Housesystems-ActivationHouses-Multiple-Paid Social' 
)