Hi all, this is my first community support question so please give me feedback if there's something I could improve on with my questioning. Loving retool!
I'm building a Cohort dashboard and as part of that I've got a relatively complex query pulling information from our Postgres DB. To date we've been running a query manually each month and populating a spreadsheet and through forgetfulness it's not something we keep ontop of, so automating this via retool is the way forward.
Challenge: Rows of our cohort table represent months when users joined and they ofcourse are dynamic already. Columns represent the revenue and activity of the users in the months following them joining the platform. I'd like to use a JS query to create a variable that I can use in my SQL query to create dynamic rows or a similar solution that achieves the end objective automatically.
I'll share the code below for review
CohortsMainQuery (postgresql)
WITH general AS (SELECT DATE_TRUNC('month', core_person.kyc_passed_at)::DATE AS cohort,
SUM(invoices_earninginvoice.total) / 100.0 AS totalvol,
COUNT(invoices_earninginvoice.total) AS totalcount,
AVG(invoices_earninginvoice.total) / 100.0 AS averagetxval
FROM core_person
JOIN invoices_earninginvoice ON core_person.id = invoices_earninginvoice.creator_id
WHERE invoices_earninginvoice.status = 2
AND invoices_earninginvoice.paid_at IS NOT NULL
GROUP BY cohort),
creators AS (SELECT creator_id,
DATE_TRUNC('month', p.kyc_passed_at)::DATE AS cohort,
SUM(total) / 100.0 AS total,
COUNT(total) AS count
FROM invoices_earninginvoice
JOIN core_person p ON invoices_earninginvoice.creator_id = p.id
WHERE status = 2
AND type NOT IN (3, 7)
AND paid_at IS NOT NULL
AND is_creator IS TRUE
GROUP BY creator_id, p.kyc_passed_at),
monthly_activity AS (SELECT creator_id,
DATE_TRUNC('month', invoices_earninginvoice.paid_at)::DATE AS activity_month,
COUNT(*) AS transactions,
SUM(total) / 100.0 AS volume
FROM invoices_earninginvoice
WHERE status = 2
AND paid_at IS NOT NULL
AND type NOT IN (3, 7)
GROUP BY creator_id, activity_month),
activity_summary AS (SELECT c.cohort,
c.creator_id,
a.activity_month,
a.transactions,
a.volume
FROM creators c
LEFT JOIN monthly_activity a ON c.creator_id = a.creator_id),
cohort_active AS (SELECT cohort,
'active' AS data,
{{ActiveCountMonths.data}}
FROM activity_summary
GROUP BY cohort),
cohort_rev AS (SELECT cohort,
'rev' AS data,
{{RevenueMonths.data}}
FROM activity_summary
GROUP BY cohort)
SELECT
g.cohort,
g.totalvol,
g.totalcount,
g.averagetxval,
AVG(c.total) AS LTV,
AVG(c.count) AS LTC,
ca.data,
{{MonthsFinal.data}}
FROM general g
JOIN creators c ON c.cohort = g.cohort
LEFT JOIN cohort_active ca ON ca.cohort = g.cohort
GROUP BY g.cohort, g.totalvol, g.totalcount, g.averagetxval, ca.data, {{MonthsFinal.data}}
UNION ALL
SELECT
g.cohort,
NULL AS totalvol,
NULL AS totalcount,
NULL AS averagetxval,
NULL AS LTV,
NULL AS LTC,
cr.data,
{{MonthsFinal.data}}
FROM general g
JOIN creators c ON c.cohort = g.cohort
LEFT JOIN cohort_rev cr ON cr.cohort = g.cohort
GROUP BY g.cohort, g.totalvol, g.totalcount, g.averagetxval, cr.data, {{MonthsFinal.data}}
ORDER BY cohort, data desc
ActiveCountMonths (JS Query)
function generateSqlCountDistinct(startDate, endDate) {
let start = new Date(startDate);
let end = new Date(endDate);
let sqlSnippets = [];
for (let d = new Date(start); d <= end; d.setMonth(d.getMonth() + 1)) {
let month = String(d.getMonth() + 1).padStart(2, '0');
let year = d.getFullYear();
let monthName = d.toLocaleString('en-US', { month: 'short' });
let columnName = `${monthName}_${year}`;
sqlSnippets.push(`COUNT(DISTINCT creator_id) FILTER (WHERE activity_month = '${year}-${month}-01') AS ${columnName}`);
}
return sqlSnippets.join(",\n");
}
const today = new Date();
const currentMonth = new Date(today.getFullYear(), today.getMonth(), 1);
let sqlCountDistinct = generateSqlCountDistinct('2021-11-01', currentMonth.toISOString().split('T')[0]);
return sqlCountDistinct;
RevenueMonths (JS Query)
function generateSqlCountDistinct(startDate, endDate) {
let start = new Date(startDate);
let end = new Date(endDate);
let sqlSnippets = [];
for (let d = new Date(start); d <= end; d.setMonth(d.getMonth() + 1)) {
let month = String(d.getMonth() + 1).padStart(2, '0');
let year = d.getFullYear();
let monthName = d.toLocaleString('en-US', { month: 'short' });
let columnName = `${monthName}_${year}`;
sqlSnippets.push(`SUM(volume) FILTER (WHERE activity_month = '${year}-${month}-01') AS ${columnName}`);
}
return sqlSnippets.join(",\n");
}
const today = new Date();
const currentMonth = new Date(today.getFullYear(), today.getMonth(), 1);
let sqlCountDistinct = generateSqlCountDistinct('2021-11-01', currentMonth.toISOString().split('T')[0]);
return sqlCountDistinct;
MonthsFinal (JS Query)
function generateColumnNames(startDate, endDate) {
let start = new Date(startDate);
let end = new Date(endDate);
let columnNames = [];
for (let d = new Date(start); d <= end; d.setMonth(d.getMonth() + 1)) {
let monthName = d.toLocaleString('en-US', { month: 'short' });
let year = d.getFullYear();
columnNames.push(`${monthName}_${year}`);
}
return columnNames.join(", ");
}
const today = new Date();
const currentMonth = new Date(today.getFullYear(), today.getMonth(), 1);
let columnNames = generateColumnNames('2021-11-01', currentMonth.toISOString().split('T')[0]);
return columnNames;
The result of the above gives me a column called ?column? and the data in every row is
Nov_2021, Dec_2021, Jan_2022, Feb_2022, Mar_2022, Apr_2022, May_2022, Jun_2022, Jul_2022, Aug_2022, Sep_2022, Oct_2022, Nov_2022, Dec_2022, Jan_2023, Feb_2023, Mar_2023, Apr_2023, May_2023, Jun_2023, Jul_2023, Aug_2023, Sep_2023, Oct_2023, Nov_2023, Dec_2023, Jan_2024
FYI, run manually, the output result looks like this
If this has been answered somewhere else before, please point me in the direction, I promise I've been searching this issue for ages haha!
Many thanks in advance for your time and thoughts on this!