Skip to content

Instantly share code, notes, and snippets.

@aconfee
Created February 10, 2017 21:48
Show Gist options
  • Save aconfee/50e6768fc4fdf8850f8a4ddc6f17ba42 to your computer and use it in GitHub Desktop.
Save aconfee/50e6768fc4fdf8850f8a4ddc6f17ba42 to your computer and use it in GitHub Desktop.
$@"INSERT INTO omnicounts (
createduserid,
dealerid,
userid,
buylistitems7d,
buylistitems14d,
buylistitems30d,
buylist7d,
buylist14d,
buylist30d,
createdsavedsearch7d,
createdsavedsearch14d,
createdsavedsearch30d,
executedsavedsearch7d,
executedsavedsearch14d,
executedsavedsearch30d,
logdate
)
SELECT * FROM (
SELECT
a.dealerid,
a.userid,
COUNT(CASE WHEN a.createddate BETWEEN {lastWeek} AND CURRENT_DATE THEN 1 ELSE NULL END) AS createdsavedsearch7d,
COUNT(CASE WHEN a.createddate BETWEEN {lastHalfMonth} AND CURRENT_DATE THEN 1 ELSE NULL END) AS createdsavedsearch14d,
COUNT(CASE WHEN a.createddate BETWEEN {lastMonth} AND CURRENT_DATE THEN 1 ELSE NULL END) AS createdsavedsearch30d,
COUNT(CASE WHEN a.lastvieweddate BETWEEN {lastWeek} AND CURRENT_DATE THEN 1 ELSE NULL END) AS executedsavedsearch7d,
COUNT(CASE WHEN a.lastvieweddate BETWEEN {lastHalfMonth} AND CURRENT_DATE THEN 1 ELSE NULL END) AS executedsavedsearch14d,
COUNT(CASE WHEN a.lastvieweddate BETWEEN {lastMonth} AND CURRENT_DATE THEN 1 ELSE NULL END) AS executedsavedsearch30d,
CURRENT_DATE
FROM omni.savedsearches a
GROUP BY a.dealerid, a.userid;
),
SELECT * FROM (
SELECT
a.dealerid,
a.userid,
COUNT(CASE WHEN a.created BETWEEN {lastWeek} AND CURRENT_DATE THEN 1 ELSE NULL END) AS buylist7d,
COUNT(CASE WHEN a.created BETWEEN {lastHalfMonth} AND CURRENT_DATE THEN 1 ELSE NULL END) AS buylist14d,
COUNT(CASE WHEN a.created BETWEEN {lastMonth} AND CURRENT_DATE THEN 1 ELSE NULL END) AS buylist30d,
CURRENT_DATE
FROM omni.buylists a
GROUP BY a.dealerid, a.userid;
),
SELECT * FROM (
SELECT
a.createduserid,
COUNT(CASE WHEN a.createddate BETWEEN {lastWeek} AND CURRENT_DATE THEN 1 ELSE NULL END) AS buylistitems7d,
COUNT(CASE WHEN a.createddate BETWEEN {lastHalfMonth} AND CURRENT_DATE THEN 1 ELSE NULL END) AS buylistitems14d,
COUNT(CASE WHEN a.createddate BETWEEN {lastMonth} AND CURRENT_DATE THEN 1 ELSE NULL END) AS buylistitems30d,
CURRENT_DATE
FROM omni.buylistitems a
WHERE a.removed = 0
GROUP BY a.createduserid;
)";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment