SQL Queries Metabase Ahoy

SELECT "public"."ahoyevents"."properties"->'url' AS "propertiesurl", count(*) AS "count"
FROM "public"."ahoyevents"
GROUP BY "public"."ahoyevents"."properties"->'url'
ORDER BY "public"."ahoy_events"."properties"->'url' ASC

SELECT "ahoyevents"."id" AS "id",
"ahoyevents"."id" as "id",
"ahoyevents"."properties"->'url' as "url",
"ahoyevents"."properties"->'page' as "page"
FROM "ahoy_events"
LIMIT 2000

SELECT "public"."ahoyevents"."id" AS "id", "public"."ahoyevents"."name" AS "name", "public"."ahoyevents"."profileid" AS "profileid",
"public"."ahoyevents"."properties" AS "properties",
"public"."ahoyevents"."properties"->'url' AS "propertiesurl",
"public"."ahoyevents"."time" AS "time",
"public"."ahoyevents"."userid" AS "userid",
"public"."ahoyevents"."visitid" AS "visitid"
FROM "public"."ahoyevents"
WHERE "public"."ahoy_events"."name" = '$view'
LIMIT 2000

SELECT
"public"."ahoyevents"."id" AS "id",
"public"."ahoyevents"."name" AS "name",
"public"."ahoyevents"."userid" AS "userid",
"public"."ahoyevents"."profileid" AS "profileid",
"public"."ahoyevents"."visitid" AS "visitid",
"public"."ahoyevents"."properties" AS "properties",
"public"."ahoyevents"."properties"->'url' AS "propertiesurl",
"public"."ahoyevents"."time" AS "time"
FROM "public"."ahoyevents"
WHERE "public"."ahoy_events"."name" = '$view'
LIMIT 2000

COUNT, GROUP and ORDER

SELECT "public"."ahoyevents"."properties"->'url' AS "propertiesurl", count() AS "count"
FROM "public"."ahoyevents"
GROUP BY "public"."ahoyevents"."properties"->'url'
ORDER BY COUNT() DESC

LAST 7 DAYS
SELECT "public"."ahoyevents"."properties"->'category' AS "propertiescategory", count() AS "count"
FROM "public"."ahoyevents"
WHERE CAST("public"."ahoyevents"."time" AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date)
GROUP BY "public"."ahoy_events"."properties"->'category'
ORDER BY COUNT() DESC

Add Wildcards = '%server%'

SELECT "public"."ahoyevents"."name" AS "name", count(*) AS "count"
FROM "public"."ahoyevents"
WHERE NOT (lower("public"."ahoyevents"."name") like '%server%'
GROUP BY "public"."ahoyevents"."name"
ORDER BY "count" DESC, "public"."ahoy_events"."name" ASC

SELECT "public"."ahoyevents"."id" AS "id", "public"."ahoyevents"."name" AS "name", "public"."ahoyevents"."profileid" AS "profileid", "public"."ahoyevents"."properties" AS "properties", "public"."ahoyevents"."time" AS "time", "public"."ahoyevents"."userid" AS "userid", "public"."ahoyevents"."visitid" AS "visitid"
FROM "public"."ahoyevents"
WHERE NOT (lower("public"."ahoy_events"."name") like ?)
LIMIT 2000

SELECT "public"."ahoyevents"."name" AS "name"
FROM "public"."ahoyevents"
WHERE (CAST("public"."ahoyevents"."time" AS date) BETWEEN CAST((NOW() + INTERVAL '-30 day') AS date)
AND CAST(now() AS date) AND NOT (lower("public"."ahoyevents"."name") like ?))
GROUP BY "public"."ahoyevents"."name"
ORDER BY "public"."ahoyevents"."name" ASC
LIMIT 2000

SELECT "public"."ahoyevents"."properties"->'category' AS "propertiescategory", count() AS "count"
FROM "public"."ahoyevents"
WHERE (CAST("public"."ahoyevents"."time" AS date) BETWEEN CAST((NOW() + INTERVAL '-30 day') AS date)
AND CAST(now() AS date) AND NOT (lower("public"."ahoyevents"."name") like ?))
GROUP BY "public"."ahoyevents"."properties"->'category'
ORDER BY COUNT() DESC

SELECT "public"."ahoyevents"."properties"->'category' AS "propertiescategory", count() AS "count"
FROM "public"."ahoyevents"
WHERE (CAST("public"."ahoyevents"."time" AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date) AND NOT (lower("public"."ahoyevents"."properties"::json->>'category') like'))
GROUP BY "public"."ahoyevents"."properties"->'category'
ORDER BY COUNT() DESC

SELECT "public"."ahoyevents"."properties"->'category' AS "propertiescategory", count() AS "count"
FROM "public"."ahoyevents"
WHERE "public"."ahoyevents"."properties"->'category' is not null
GROUP BY "public"."ahoy_events"."properties"->'category'
ORDER BY COUNT() DESC

SELECT "public"."ahoyevents"."properties"->'category' AS "propertiescategory", count() AS "count"
FROM "public"."ahoyevents"
WHERE NOT (lower("public"."ahoyevents"."properties"->'category') like '%server%')
GROUP BY "public"."ahoy_events"."properties"->'category'
ORDER BY COUNT() DESC

--- SQL and jsonb ---

WHERE "public"."ahoyevents"."properties"->'category' is not null
WHERE "public"."ahoyevents"."properties"::json->>'category' like '%server%'

--- LAST vs THIS PERIOD

SELECT Spendenquartal, t2017.sum AS Durchschnittspende2017, t2016.sum AS Durchschnittspende2016, concat(round((t2017.sum-t2016.sum)/t2016.sum*100), “%”) AS WachstumzumVorjahr
FROM
(
SELECT sum(donationsum)/count(donationid) AS sum, quarter(date) AS quarter1, concat(dateyear, " - Q", quarter(date)) AS Spendenquartal
FROM donationtable
WHERE strtodate(concat(dateformat(donationtable.date, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) BETWEEN strtodate(concat(dateformat(dateadd(now(), INTERVAL -4 quarter), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
AND strtodate(concat(dateformat(dateadd(now(), INTERVAL -1 quarter), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
GROUP BY quarter1
Order by dateyear asc, quarter(date) asc
) AS t2017
LEFT JOIN
(
SELECT sum(donationsum)/count(donationid) AS sum, quarter(date) AS quarter2
FROM donationtable
WHERE strtodate(concat(dateformat(donationtable.date, ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’) BETWEEN strtodate(concat(dateformat(dateadd(now(), INTERVAL -8 quarter), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
AND strtodate(concat(dateformat(dateadd(now(), INTERVAL -5 quarter), ‘%Y-%m’), ‘-01’), ‘%Y-%m-%d’)
GROUP BY quarter2
Order by date_year asc, quarter(date) asc
) AS t2016
ON t2017.quarter1 = t2016.quarter2

--- FUNNEL ---

select

count(distinct id),
case
when "public"."ahoyevents"."properties"::json->>'category' = 'Sign in' Then 'A - Sign in'
when "public"."ahoyevents"."properties"::json->>'category' = 'Add project' Then 'B - Add project'
end as step
from "public"."ahoyevents"
where datetime > currentdate() - interval 1 month
group by "public"."ahoyevents"."properties"::json->>'category'
order by case
when "public"."ahoyevents"."properties"::json->>'category' = 'Sign in' Then 'A - Sign in'
when "public"."ahoy_events"."properties"::json->>'category' = 'Add project' Then 'B - Add project'
end

select

count(distinct id),
case
when event = 'Sign in' Then 'A - Sign in'
when event = 'Add project' Then 'B - Add project'
when event = 'Track time' Then 'C - Track time'
when event = 'Submit timesheet' Then 'D - Submit timesheet'
end as step
from events
where datetime > current_date() - interval 1 month
group by event
order by case
when event = 'Sign in' Then 'A - Sign in'
when event = 'Add project' Then 'B - Add project'
when event = 'Track time' Then 'C - Track time'
when event = 'Submit timesheet' Then 'D - Submit timesheet'
end

select

count(distinct id),
case
when "public"."ahoyevents"."properties"->'category' = '"web-homes-index"' Then '"web-homes-index"'
when "public"."ahoyevents"."properties"->'category' = '"web-searches-showcategorygeo"' Then '"web-searches-showcategorygeo"'
end as step
from "public"."ahoyevents"
where datetime > currentdate() - interval 1 month
group by "public"."ahoyevents"."properties"->'category'
order by case
when "public"."ahoyevents"."properties"->'category' = '"web-homes-index"' Then '"web-homes-index"'
when "public"."ahoyevents"."properties"->'category' = '"web-searches-showcategorygeo"' Then '"web-searches-showcategory_geo"'
end

SELECT "public"."ahoyevents"."properties"->'category' AS "propertiescategory", count() AS "count"
FROM "public"."ahoyevents"
WHERE (CAST("public"."ahoyevents"."time" AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date) AND NOT (lower("public"."ahoyevents"."properties"::json->>'category') like '%server%'))
GROUP BY "public"."ahoyevents"."properties"->'category'
ORDER BY COUNT() DESC

select
count(distinct id),
case
when "public"."ahoyevents"."properties"::json->>'category' = 'web-homes-index' Then 'web-homes-index'
when "public"."ahoyevents"."properties"::json->>'category' = 'web-searches-showcategorygeo' Then 'web-searches-showcategorygeo'
end as step
from "public"."ahoyevents"
group by "public"."ahoyevents"."properties"::json->>'category'
order by case
when "public"."ahoyevents"."properties"::json->>'category' = 'web-homes-index' Then 'web-homes-index'
when "public"."ahoyevents"."properties"::json->>'category' = 'web-searches-showcategorygeo' Then 'web-searches-showcategorygeo'
end

group by userid or visitorid

-- funnel should have users that did both, not just event funnel

SELECT
s.sessionid
, LEAST(MAX(pv.sentat) + INTERVAL '30 minutes', MIN(s.nextsessionstart)) AS endedat
, COUNT(DISTINCT pv.eventid) AS numevents
, COUNT(CASE WHEN pv.event = 'viewbuypage' THEN eventid ELSE null END) AS cntviewbuypage
, COUNT(CASE WHEN pv.event = 'addeditem' THEN eventid ELSE null END) AS cntaddeditem
, COUNT(CASE WHEN pv.event = 'tappedshipit' THEN eventid ELSE null END) AS cntshipit
, COUNT(CASE WHEN pv.event = 'madepurchase' THEN eventid ELSE null END) AS cntmadepurchase
FROM ${sessions.SQLTABLENAME} AS s
LEFT JOIN ${sessionsmap.SQLTABLENAME} AS map USING(sessionid)
LEFT JOIN ${mappedtracks.SQLTABLENAME} AS pv USING(eventid)
GROUP BY 1

---- PIVOT TABLE ---

visit_id step 1, step 2, step 3, step 4

SELECT "public"."ahoyevents"."visitid" AS "visitid", "public"."ahoyevents"."name" AS "name", count(*) AS "count"
FROM "public"."ahoyevents"
GROUP BY "public"."ahoyevents"."visitid", "public"."ahoyevents"."name"
ORDER BY "public"."ahoyevents"."visitid" ASC, "public"."ahoy_events"."name" ASC

--- CROSS TABS ---

https://www.compose.com/articles/metrics-maven-creating-pivot-tables-in-postgresql-using-crosstab/

select * from crosstab (

'select distinct
productline,
category,
round(avg(price),2) as avgprice

from catalog
group by
productline,
category
order by productline',

'select distinct category from catalog order by 1'
)
AS (
productline character varying,
dogtoysavgprice numeric,
dogwearavg_price numeric
)
;

select * from crosstab (

'SELECT "public"."ahoyevents"."visitid" AS "visitid", "public"."ahoyevents"."name" AS "name", count(*) AS "count"
FROM "public"."ahoyevents"
GROUP BY "public"."ahoyevents"."visitid", "public"."ahoyevents"."name"
ORDER BY "public"."ahoyevents"."visitid" ASC, "public"."ahoy_events"."name" ASC',

' SELECT distinct "public"."ahoyevents"."name" FROM "public"."ahoyevents'
)
AS (
name text,
visit_id numeric
);

--
SELECT "public"."ahoyevents"."visitid"
FROM "public"."ahoyevents"
LEFT JOIN "public"."ahoyvisits"
ON "public"."ahoyvisits"."id" = "public"."ahoyevents"."visit_id"

AND t2.event_id != "public"."ahoy_events".event_id

WHERE t1.eventid = 'someid'
AND t2.eventid IS NULL;

--- LEFT JOIN ---
SELECT "public"."ahoyvisits"."id" AS "visitid", "public"."ahoyevents"."id" AS "eventid"
FROM "public"."ahoyvisits"
LEFT JOIN "public"."ahoyevents"
ON "public"."ahoyevents"."visitid" = "public"."ahoy_visits"."id"

SELECT Student,
COUNT(DISTINCT Course) AS NumberOfCourses,
COUNT(DISTINCT University) AS NumberOfUniversities
FROM YourTable
GROUP BY Student

SELECT "public"."ahoyevents"."visitid",
COUNT(DISTINCT name) AS NumberOfEvents,
FROM "public"."ahoyevents"
GROUP BY "public"."ahoyevents"."visit_id"

--- simplified ---
SELECT "visitid",
COUNT(DISTINCT name) AS NumberOfEvents
FROM "public"."ahoyevents"
GROUP BY "visit_id"

SELECT "visitid",
COUNT(name = '$view') AS visitname,
COUNT(name = '$view') AS visitname
FROM "public"."ahoyevents"
GROUP BY "visitid"
ORDER BY "visitid"

select distinct visitid
from "public"."ahoyevents"
where name = '$view'
and time between '2017-09-01'::timestamp and now()
order by visit_id

---- http://www.silota.com/docs/recipes/sql-funnel-analysis.html ----

select distinct ṿisitid
from "public"."ahoyevents" s2
inner join
(
select distinct visitid
from "public"."ahoyevents"
where name = '$view'
and time between '2017-09-01'::timestamp and now()
) s1
on s1.visitid = s2.visitid
where s2.name = '$click'

select distinct userid
from events s2
inner join
(
select distinct userid
from events
where event = 'View Product'
and timestamp between '2017-09-01'::timestamp and now()
) s1
on s1.userid = s2.userid
and s2.timestamp < (s1.timestamp + '7 days'::interval)
-- and s2.timestamp > s1.timestamp
where s2.event = 'Add to Cart'

with s2 as (
select distinct s2.visitid
from "public"."ahoyevents" s2
inner join
(
select distinct "public"."ahoyevents"."visitid"
from "public"."ahoyevents"
where "public"."ahoyevents"."name" = '$view'
and time between '2017-09-01'::timestamp and now()
) s1
on s1.visitid = s2.visitid
where s2.name = '$click'
and s2.time between '2017-09-01'::timestamp and now()
order by s2.visit_id
)

SELECT '$view' as step, count(*) as cnt FROM s2;

select 'View Product' as step, count() as cnt from s1
union
select 'Add to Cart' as step, count() as cnt from s2
union
select 'Checkout' as step, count(*) as cnt from s3

--- s1 s2 s3 funnel ---

with s1 as (
select distinct visitid
from "public"."ahoyevents"
WHERE (CAST(time AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date) AND (lower(properties::json->>'category') like '%web-homes%'))
), s2 as (
select distinct s2.visitid
from "public"."ahoyevents" s2
inner join
(
select distinct visitid
from "public"."ahoyevents"
WHERE (CAST(time AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date) AND (lower(properties::json->>'category') like '%web-homes%'))
) s1
on s1.visitid = s2.visitid
WHERE (CAST(time AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date) AND (lower(properties::json->>'category') like '%web-searches%'))
) s3 as (
select distinct s3.visitid
from "public"."ahoyevents" s3
inner join
(
select distinct s2.visitid
from "public"."ahoyevents" s2
inner join
(
select distinct visitid
from "public"."ahoyevents"
WHERE (CAST(time AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date) AND (lower(properties::json->>'category') like '%web-homes%'))
) s1
on s1.visitid = s2.visitid
WHERE (CAST(time AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date) AND (lower(properties::json->>'category') like '%web-searches%'))
) s2
on s2.visitid = s3.visitid
WHERE (CAST(time AS date) BETWEEN CAST((NOW() + INTERVAL '-7 day') AS date)
AND CAST(now() AS date) AND (lower(properties::json->>'category') like '%web-profiles%'))
)

select 'web-homes' as step, count() as cnt from s1
union
select 'web-searches' as step, count() as cnt from s2
union
select 'web-profiles' as step, count(*) as cnt from s3

Autocomplete Clicks by

Group by autocompleteurl
autocompleteurl, name, type.

SELECT "public"."ahoyevents"."properties"->'autocompletename' AS "autocompletename", "public"."ahoyevents"."properties"->'autocompleteurl' AS "autocompleteurl", "public"."ahoyevents"."properties"->'autocompletetype' AS "autocompletetype", count(*) AS "count"
FROM "public"."ahoyevents"
WHERE (("public"."ahoyevents"."properties"->'autocompletename' is not null)
AND CAST("public"."ahoyevents"."time" AS date) BETWEEN CAST((NOW() + INTERVAL '-{{numberofdays}} day') AS date) AND CAST(now() AS date))
GROUP BY "public"."ahoyevents"."properties"->'autocompletename', "public"."ahoyevents"."properties"->'autocompleteurl', "public"."ahoyevents"."properties"->'autocompletetype'
ORDER BY "count" DESC, "public"."ahoyevents"."properties"->'autocomplete_name' ASC