Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?

Introduction

Ankit and I spoke on 2018-02-01 and 2018-02-23 about the future of TWS. Both of us have been hands-off for a while, and only a few cities are still active. Here’s the rundown on how various parts of the Tea With Strangers Org are performing.

New User & Host Acquisition

Total number of users since the beginning of 2018 and the cities where more than five people have signed up, and the total number of users in that city since TWS’ inception:

SELECT c.name AS "City", count(*) AS "Signups", c.users_count AS "Total Users" FROM users as u
JOIN cities AS c ON (c.id = u.home_city_id)
WHERE u.created_at >= date('2018-01-01')
GROUP BY c.name, c.users_count
HAVING count(*) > 5
CitySignupsTotal Users
Boston72196
DC401461
London372427
New York City394240
SF / Bay Area467200

New users interested in hosting since the beginning of the year:

SELECT c.name AS "City", c.users_count AS "Total Users", count(*) AS "New Users Interested in Hosting" FROM users AS u
JOIN cities AS c ON (c.id = u.home_city_id)
WHERE (tws_interests ->> 'hosting')::boolean AND u.created_at >= date('2018-01-01')
GROUP BY c.name, c.users_count
ORDER BY "Total Users" DESC
--HAVING count(*) > 1
CityTotal UsersUsers Interested in Hosting
SF / Bay Area72006
New York City42406
London24272
DC14612
Seattle5021
Austin1811
Mumbai381
Barcelona21
Columbus21
copenhagen21
Gurgaon21
Hong Kong21
Jerusalem21
Ukiah21
Wellington, New Zealand21

In short, it appears focusing on pre-existing cities is prudent.

Tea Times In Last Ninety Days

Next up was examining how active each existing city was; we want to see how many tea times occurred where people attended, and how many people were waitlisted for those tea times:

SELECT c.name,
count(*) AS "Attended Tea Times",
AVG(a.attended)::int AS "Avg. Attendees",
AVG(a.waitlisted)::int AS "Avg. Waitlisted Attendees"
FROM tea_times
JOIN cities AS c ON (c.id = tea_times.city_id)
RIGHT JOIN (
 SELECT tea_time_id,
 SUM(case when status in (0,1,3) then 1 else 0 end) as attended,
 SUM(case when status = 4 then 1 else 0 end) AS waitlisted
 FROM attendances
 group by tea_time_id
) as a ON (tea_times.id = a.tea_time_id)
WHERE start_time >= (current_date - 90) AND followup_status != 3
GROUP BY c.name
ORDER BY "Attended Tea Times" DESC
-- SELECT c.name,
-- CASE
--   WHEN a.status IN (0,1,3) THEN 'Attended'
--   WHEN a.status = 4 THEN 'Waitlisted'
--   ELSE 'Other'
-- END AS "Attendance Status",
-- COUNT(*) FROM tea_times
-- JOIN cities AS c ON (c.id = tea_times.city_id)
-- JOIN attendances AS a ON (a.tea_time_id = tea_times.id)
-- WHERE start_time >= (current_date - 90)
-- GROUP BY ROLLUP(c.name, "Attendance Status")
nameAttended Tea TimesAvg. AttendeesAvg. Waitlisted Attendees
London1730
SF / Bay Area752
Boston120
DC164
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment