SQL error in derived table

Hi,

I am connecting to an Athena database. I have created an array which simply lists the dates from 2000 to 2080 with one row per day, and want to cross join this with some information about customers, to find out how many active customers there were on one day. The nested query (creating the date array works by itself.

I am having the error : Column 'day_array.date' cannot be resolved

when trying to execute the following in SQL runner:

WITH day_array AS (SELECT
  count(DISTINCT customer_id) AS active_customers
  ,day_from_array
--  ,is_test_license
 -- ,license_expire_date
FROM database.table
CROSS JOIN
(SELECT
      CAST(date_column AS DATE) day_from_array
          FROM (
            VALUES (SEQUENCE(FROM_ISO8601_DATE('2000-01-01'),
                   FROM_ISO8601_DATE('2080-12-31'), INTERVAL '1' DAY) ) ) AS t1(date_array)
    CROSS JOIN UNNEST(date_array) AS t2(date_column) )
WHERE NOT is_test_license
AND user_since_date <= day_from_array
AND license_expire_date > day_from_array

GROUP BY 2
ORDER BY 2 asc)
SELECT
    (DATE_FORMAT(CAST(day_array.date  AS TIMESTAMP), '%Y-%m-%d')) AS "day_array.day_from_array",
    day_array.active_customers  AS "day_array.active_customers"
FROM day_array
GROUP BY
    1,
    2
ORDER BY
    1 DESC
LIMIT 500

0 0 136
0 REPLIES 0
Top Labels in this Space
Top Solution Authors