Add order by time to array_sort(collect ) in sql for Amazon advertising data

19 Views Asked by At

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: enter image description here

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

0

There are 0 best solutions below