-- raw sql results do not include filled-in values for 'employers_test.install_time_group_week'


SELECT
	DATE_FORMAT(DATE_TRUNC('DAY', DATE_ADD('day', (0 - MOD((DAY_OF_WEEK((cast(employers_test.install_time as timestamp) AT TIME ZONE 'Europe/Luxembourg')) % 7) - 1 + 7, 7)), (cast(employers_test.install_time as timestamp) AT TIME ZONE 'Europe/Luxembourg'))), '%Y-%m-%d') AS "employers_test.install_time_group_week",
--	COALESCE(
--	  COALESCE(
--	    (
	        SUM(DISTINCT
	          CAST(
	            FLOOR(
	              COALESCE(employers_test.appsflyer_installs ,0)*(1000000*1.0)
	            )
	            AS DECIMAL(38,0)
	          )
	        ) AS "foo"
,
        SUM(DISTINCT
	        (
	          CAST(
	            FLOOR(
	              COALESCE(employers_test.appsflyer_installs ,0)*(1000000*1.0)
	            )
	            AS DECIMAL(38,0)
	          )
	        )
	        +
	        (
	          CAST(
	            FROM_BASE(
	              SUBSTR(
	                TO_HEX(
	                  MD5(
	                    CAST(
	                      CAST(employers_test.user_id  AS VARCHAR)
	                      AS VARBINARY
	                    )
	                  )
	                )
	                ,1,14
	              )
	              ,16
	            )
	            AS DECIMAL(38, 0)
	          )
	          *
	          CAST(10000000000 AS DECIMAL(38, 0))
	          +
	          CAST(
	            FROM_BASE(
	              SUBSTR(
	                TO_HEX(
	                  MD5(
	                    CAST(
	                      CAST(employers_test.user_id  AS VARCHAR)
	                      AS VARBINARY
	                    )
	                  )
	                )
	                , 17, 10
	              )
	              , 16
	            )
	            AS DECIMAL(38, 0)
	          )
	        )
	      )
--	      -
,
	      SUM(DISTINCT
	        (
	          CAST(
	            FROM_BASE(
	              SUBSTR(
	                TO_HEX(
	                  MD5(
	                    CAST(
	                      CAST(employers_test.user_id  AS VARCHAR)
	                      AS VARBINARY
	                    )
	                  )
	                )
	                ,1,14
	              )
	              ,16
	            )
	            AS DECIMAL(38, 0)
	          )
	          *
	          CAST(10000000000 AS DECIMAL(38, 0))
	          +
	          CAST(
	            FROM_BASE(
	              SUBSTR(
	                TO_HEX(
	                  MD5(
	                    CAST(
	                      CAST(employers_test.user_id  AS VARCHAR)
	                      AS VARBINARY
	                    )
	                  )
	                )
	                , 17, 10
	              )
	              , 16
	            )
	            AS DECIMAL(38, 0)
	          )
	        )
	      )
--	    )
--	    /
--	    (1000000*1.0)
--	    , 0
--	  )
--	  ,0
--	)
	AS "employers_test.appsflyer_install_count"
FROM "td-presto".attribution.users_installs  AS employers_test
LEFT JOIN "td-presto".backend.users  AS employers ON employers.id = employers_test.user_id
LEFT JOIN "td-presto".backend.jobs  AS jobs ON employers.id = jobs.job_owner_id

WHERE
	(((cast(employers_test.install_time as timestamp)) >= ((CAST(CONCAT(DATE_FORMAT(DATE_ADD('week', -3, DATE_TRUNC('DAY', DATE_ADD('day', (0 - MOD((DAY_OF_WEEK(CAST((NOW() AT TIME ZONE 'Europe/Luxembourg') AS DATE)) % 7) - 1 + 7, 7)), CAST((NOW() AT TIME ZONE 'Europe/Luxembourg') AS DATE)))), '%Y-%m-%d %T '), 'Europe/Luxembourg') AS TIMESTAMP))) AND (cast(employers_test.install_time as timestamp)) < ((CAST(CONCAT(DATE_FORMAT(DATE_ADD('week', 4, DATE_ADD('week', -3, DATE_TRUNC('DAY', DATE_ADD('day', (0 - MOD((DAY_OF_WEEK(CAST((NOW() AT TIME ZONE 'Europe/Luxembourg') AS DATE)) % 7) - 1 + 7, 7)), CAST((NOW() AT TIME ZONE 'Europe/Luxembourg') AS DATE))))), '%Y-%m-%d %T '), 'Europe/Luxembourg') AS TIMESTAMP)))))
GROUP BY 1
ORDER BY 1 DESC
LIMIT 500