I have the following query which determines the exposure to platforms. I think currently it is being ordered alphabetically on Platform only.
I need to add order to exposure by impression_t.
WITH DS_CAMPAIGNS AS
(select
campaign
from table1
),
Sp_CAMPAIGNS AS
(select
campaign
from table2
),
IMPR_CTE AS (
SELECT
USER_ID,
ARRAY_SORT(COLLECT(DISTINCT Platform)) AS EXPOSURE,
MIN(IMPRESSION_T) AS MIN_IMPRESSION_T,
SUM(IMPRESSIONS) AS IMPRESSIONS,
SUM(TOTAL_COST) as TOTAL_COST
FROM
(
SELECT
I.USER_ID,
'DS' AS Platform,
I.IMPRESSIONS,
TOTAL_COST / 100000 AS TOTAL_COST,
I.IMPRESSION_T
FROM
table1 I
UNION ALL
SELECT
I.USER_ID,
I.Platform,
I.IMPRESSIONS,
TOTAL_COST / 100000 AS TOTAL_COST,
I.EVENT_T AS IMPRESSION_T
FROM
table2 I
)
GROUP BY
1
)
SELECT
I.EXPOSURE,
USERS_PURCHASED
EXPOSED_USERS AS UNIQUE_REACH,
TOTAL_PURCHASES,
SALES AS PRODUCT_SALES,
TOTAL_COST
FROM
(
SELECT
EXPOSURE,
COUNT(DISTINCT USER_ID) AS AD_EXPOSED_USERS,
SUM(TOTAL_COST) AS TOTAL_COST
FROM
IMPR_CTE
GROUP BY
1
) I
INNER JOIN (
SELECT
I.EXPOSURE,
COUNT(DISTINCT P.USER_ID) AS USERS_PURCHASED,
SUM(P.TOTAL_PURCHASES) AS TOTAL_PURCHASES,
SUM(P.sales) AS SALES,
FROM
table3 P
INNER JOIN IMPR_CTE I ON I.USER_ID = P.USER_ID
WHERE
CONVERSION_EVENT_DT > MIN_IMPRESSION_DT
GROUP BY
1
) P ON I.EXPOSURE = P.EXPOSURE
This is what I'm getting from the above query:

what should I add to the query to have it output exposure order by impression_t ?