Column name is ambiguous in bigquery-CodePudding

I am implementing the following solution: https://stackoverflow.com/a/32663098/19903400

Here is the code which I copied from that accepted answer, and used my datasource instead:

SELECT 
  date,
  SUM(CASE WHEN period = 7  THEN users END) as days_07,
  SUM(CASE WHEN period = 14 THEN users END) as days_14,
  SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
  SELECT
    dates.date as date,  
    periods.period as period,
    EXACT_COUNT_DISTINCT(activity.user_pseudo_id) as users
  FROM `rayn-deen-app.analytics_317927526.events_*` as activity
  CROSS JOIN (SELECT  DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY) as date FROM `rayn-deen-app.analytics_317927526.events_*` GROUP BY date) as dates
  CROSS JOIN (SELECT period FROM (SELECT 7 as period), 
                (SELECT 14 as period),(SELECT 30 as period)) as periods
  WHERE dates.date >= activity.date 
  AND INTEGER(FLOOR(DATEDIFF(dates.date, activity.date)/periods.period)) = 0
  GROUP BY 1,2
)
GROUP BY date
ORDER BY date DESC

But I am getting the following error:

Column name period is ambiguous at [13:22]

So it seems here is the code snippet which is problematic:

CROSS JOIN (SELECT period FROM (SELECT 7 as period), 
                (SELECT 14 as period),(SELECT 30 as period)) as periods

CodePudding user response：

If the goal is to have a fixed set of records, then you can replace this:

SELECT period FROM (SELECT 7 as period), 
                (SELECT 14 as period),(SELECT 30 as period)

with this:

SELECT period FROM (SELECT 7 as period UNION ALL 
                    SELECT 14 UNION ALL 
                    SELECT 30)