tws-talk-2018-02-23.org

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

City	Signups	Total Users
Boston	7	2196
DC	40	1461
London	37	2427
New York City	39	4240
SF / Bay Area	46	7200

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

City	Total Users	Users Interested in Hosting
SF / Bay Area	7200	6
New York City	4240	6
London	2427	2
DC	1461	2
Seattle	502	1
Austin	181	1
Mumbai	38	1
Barcelona	2	1
Columbus	2	1
copenhagen	2	1
Gurgaon	2	1
Hong Kong	2	1
Jerusalem	2	1
Ukiah	2	1
Wellington, New Zealand	2	1

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")

name	Attended Tea Times	Avg. Attendees	Avg. Waitlisted Attendees
London	17	3	0
SF / Bay Area	7	5	2
Boston	1	2	0
DC	1	6	4