Public
Cron
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
/** @jsxImportSource https://esm.sh/preact */
import { email } from "https://esm.town/v/std/email?v=11";
import { sqlite } from "https://esm.town/v/std/sqlite?v=4";
import { render } from "npm:preact-render-to-string";
type SubscriberRow = [
name: string,
email: string,
];
type VerifiedStats = [
verified: 0 | 1,
totalSubs: number,
];
export async function dailyEmailSubscriptionStats(interval: Interval) {
const { rows: newSubs } = await sqlite.execute(`
SELECT name, email
FROM subscribers
WHERE subscribed_at > datetime('now', '-1 day')
AND verified = 1;
`) as unknown as { rows: SubscriberRow[] };
const { rows: verifiedStats } = await sqlite.execute(`
SELECT verified, COUNT(*) as total_subs
FROM subscribers
GROUP BY verified;
`) as unknown as { rows: VerifiedStats[] };
const nonVerifiedSubs = verifiedStats[0][1];
const verifiedSubs = verifiedStats[1][1];
const markup = render(
<main>
<h1>New subscribers since yesterday</h1>
<ul style={{ padding: "0px" }}>
{newSubs.map(([name, email]) => <li key={email}>{name} ({email})</li>)}
</ul>
<p>Total verified subscribers: {verifiedSubs}</p>
<p>Total non-verified subscribers: {nonVerifiedSubs}</p>
</main>,
);
const today = new Date().toLocaleDateString("en-US", { month: "2-digit", day: "2-digit" });
email({
subject: `Daily subscriber stats for petemillspaugh.com (${today})`,
html: markup,
});
}
Val Town is a social website to write and deploy JavaScript.
Build APIs and schedule functions from your browser.
Comments
Nobody has commented on this val yet: be the first!
February 1, 2024