· 8 years ago · Mar 11, 2018, 06:52 AM
1-- How many rows do we have in each table?
2
3select
4 count(u.id) as users,
5 count(distinct u.id) as distinct_users,
6 count(s.user_id) as survey_responses,
7 count(distinct s.user_id) as distinct_survey_responses,
8 count(e.*) as events
9from users u, survey s, events e;
10
11-- 27,267 unique users
12-- 2,400,491 events
13-- 4,081 unique survey responses
14
15-- Why does the combined query take over 30 minutes to return when we can get the results of `select count(*)` on individual tables in like 2 seconds?
16-- Even without the events table included
17
18--------------------------------------------------
19--------------------------------------------------
20
21-- Can we use the survey data to make reliable assumptions about our population? How representative are the users in the survey?
22
23--------------------
24-- fx_version --
25--------------------
26
27with
28 population as (
29 with total as
30 (select count(*) as "total" from users)
31 select
32 fx_version,
33 count(*) as "count_population",
34 round((count(*) / avg(total.total) * 100), 2) as "percent_population"
35 from users, total
36 group by 1
37 order by 1
38 ),
39 sample as (
40 with total as
41 (select count(*) as "total" from survey)
42 select
43 fx_version,
44 count(*) as "count_sample",
45 round((count(*) / avg(total.total) * 100), 2) as "percent_sample"
46 from users, total
47 where users.id in
48 (select user_id from survey)
49 group by 1
50 order by 1
51 )
52select
53 *
54from population
55 left join sample
56 using (fx_version)
57order by 1;
58
59-- 27 FX versions starting with 3.5.11 and ending with 4.0b8pre
60-- Sample/population distribution is pretty comparable w/89% of users on 4.0b6 in both cases
61
62--------------------
63-- os --
64--------------------
65
66with
67 population as (
68 with total as
69 (select count(*) as "total" from users)
70 select
71 os,
72 count(*) as "count_population",
73 round((count(*) / avg(total.total) * 100), 2) as "percent_population"
74 from users, total
75 group by 1
76 order by 1
77 ),
78 sample as (
79 with total as
80 (select count(*) as "total" from survey)
81 select
82 os,
83 count(*) as "count_sample",
84 round((count(*) / avg(total.total) * 100), 2) as "percent_sample"
85 from users, total
86 where users.id in
87 (select user_id from survey)
88 group by 1
89 order by 1
90 )
91select
92 *
93from population
94 left join sample
95 using (os)
96order by 1;
97-- 18 OS types (3 Mac, 4 Linux, 10 Windows, 1 Sun)
98with
99 population as (
100 with total as
101 (select count(*) as "total" from users)
102 select
103 os,
104 count(*) as "count_population",
105 round((count(*) / avg(total.total) * 100), 2) as "percent_population"
106 from users, total
107 group by 1
108 order by 1
109 ),
110 sample as (
111 with total as
112 (select count(*) as "total" from survey)
113 select
114 os,
115 count(*) as "count_sample",
116 round((count(*) / avg(total.total) * 100), 2) as "percent_sample"
117 from users, total
118 where users.id in
119 (select user_id from survey)
120 group by 1
121 order by 1
122 )
123select
124 case
125 when os ilike '%mac%'
126 then 'Mac'
127 when os ilike '%linux%'
128 then 'Linux'
129 when os ilike '%windows%'
130 then 'Windows'
131 when os ilike '%sunos%'
132 then 'Sun'
133 else 'Other'
134 end as "os_group",
135 sum(count_population) as count_population,
136 sum(percent_population) as percent_population,
137 sum(count_sample) as count_sample,
138 sum(percent_sample) as percent_sample
139from (
140 select * from population left join sample using (os)
141) as discrete
142group by 1;
143-- Grouping them we can see that Mac OS users are slightly overrepresented in the sample (10% vs 5% in the population)
144-- Not a big problem but we can just keep that in mind
145
146--------------------
147-- version --
148--------------------
149
150with
151 population as (
152 with total as
153 (select count(*) as "total" from users)
154 select
155 version,
156 count(*) as "count_population",
157 round((count(*) / avg(total.total) * 100), 2) as "percent_population"
158 from users, total
159 group by 1
160 order by 1
161 ),
162 sample as (
163 with total as
164 (select count(*) as "total" from survey)
165 select
166 version,
167 count(*) as "count_sample",
168 round((count(*) / avg(total.total) * 100), 2) as "percent_sample"
169 from users, total
170 where users.id in
171 (select user_id from survey)
172 group by 1
173 order by 1
174 )
175select
176 *
177from population
178 left join sample
179 using (version)
180order by 1;
181-- 4 different test pilot extension versions
182-- 93-94% of users (sample and pop) are on the most recent version, 1.0.3
183
184--------------------
185-- extensions --
186--------------------
187
188with
189 population as (
190 with total as
191 (select count(*) as "total" from users)
192 select
193 number_extensions,
194 count(*) as "count_population",
195 round((count(*) / avg(total.total) * 100), 2) as "percent_population"
196 from users, total
197 group by 1
198 order by 1
199 ),
200 sample as (
201 with total as
202 (select count(*) as "total" from survey)
203 select
204 number_extensions,
205 count(*) as "count_sample",
206 round((count(*) / avg(total.total) * 100), 2) as "percent_sample"
207 from users, total
208 where users.id in
209 (select user_id from survey)
210 group by 1
211 order by 1
212 )
213select
214 *
215from population
216 left join sample
217 using (number_extensions)
218order by 1;
219-- The user with the most extensions has 185 extensions
220with
221 population as (
222 with total as
223 (select count(*) as "total" from users)
224 select
225 number_extensions,
226 count(*) as "count_population",
227 round((count(*) / avg(total.total) * 100), 2) as "percent_population"
228 from users, total
229 group by 1
230 order by 1
231 ),
232 sample as (
233 with total as
234 (select count(*) as "total" from survey)
235 select
236 number_extensions,
237 count(*) as "count_sample",
238 round((count(*) / avg(total.total) * 100), 2) as "percent_sample"
239 from users, total
240 where users.id in
241 (select user_id from survey)
242 group by 1
243 order by 1
244 )
245select
246 case
247 when number_extensions = 1
248 then '1. 1'
249 when number_extensions <= 5
250 then '2. 2-5'
251 when number_extensions <= 15
252 then '3. 6-15'
253 when number_extensions <= 25
254 then '4. 16-25'
255 else '5. 25+'
256 end as "number_extensions",
257 sum(count_population) as count_population,
258 sum(percent_population) as percent_population,
259 sum(count_sample) as count_sample,
260 sum(percent_sample) as percent_sample
261from (
262 select * from population left join sample using (number_extensions)
263) as discrete
264group by 1
265order by 1;
266-- This is where we have the biggest delta between sample/pop - users with more extensions were more likely to be represented in the survey
267-- Average number of extensions installed per user in sample vs population?
268select
269 avg(p.number_extensions) as population_avg_ext,
270 avg(s.number_extensions) as sample_avg_ext
271from users p,
272 (
273 select number_extensions from users
274 where users.id in (select user_id from survey)
275 ) as s;
276-- Population: 6
277-- Sample: 9
278
279--------------------------------------------------
280--------------------------------------------------
281
282-- Summary statistics based on survey data
283
284-- "How long have you used Firefox?"
285select q1, count(*), round(count(*)::numeric/(select count(*) from survey) * 100, 2) from survey group by 1 order by 1;
286-- 9% < 1 year, 91% > 1 year, 69% > 3 years (!)
287
288-- "Do you use more than one browser in your daily life?"
289-- 65% use more than one browser
290
291-- "If you use other browsers besides Firefox, what are they?"
292select
293 round((select count(*) from survey where q3 ilike '%0%')::numeric / sample.total * 100, 2) as "chrome",
294 round((select count(*) from survey where q3 ilike '%1%')::numeric / sample.total * 100, 2) as "safari",
295 round((select count(*) from survey where q3 ilike '%2%')::numeric / sample.total * 100, 2) as "opera",
296 round((select count(*) from survey where q3 ilike '%3%')::numeric / sample.total * 100, 2) as "ie",
297 round((select count(*) from survey where q3 ilike '%<%')::numeric / sample.total * 100, 2) as "other"
298from (select count(*) as "total" from survey) as sample;
299-- 46% IE, 42% Chrome, 17% Safari, 16% Opera, 5% Other (non-exclusive)
300
301-- "If you use multiple browsers, what do you consider to be your primary browser?"
302-- 17% only use Firefox, 65% Firefox, 6% Chrome, 1% Safari, 1% Opera, 3% IE
303
304-- "What is your gender?"
305-- 91% male lol
306
307-- "How old are you?"
308-- 63% between 18-35
309
310-- "How much time do you spend on the Web each day?"
311-- 89% > 2 hours, 20% > 10 hours
312
313--------------------------------------------------
314--------------------------------------------------
315
316-- Let's create our user segments
317
318-- New/casual users
319select count(*) from survey
320where (
321 (q1 = '0' or q1 = '1')
322 and
323 (q7 = '0' or q7 = '1' or q7 = '2')
324 )
325
326-- Loyal/casual users
327select count(*) from survey
328where (
329 (q1 <> '0' and q1 <> '1')
330 and
331 (q7 = '0' or q7 = '1' or q7 = '2')
332 )
333
334-- New/super users
335select count(*) from survey
336where (
337 (q1 = '0' or q1 = '1')
338 and
339 (q7 <> '0' and q7 <> '1' and q7 <> '2')
340 )
341
342-- Loyal/super users
343select count(*) from survey
344where (
345 (q1 <> '0' and q1 <> '1')
346 and
347 (q7 <> '0' and q7 <> '1' and q7 <> '2')
348 )
349
350-- Let's save the IDs of our loyal/super users
351drop table if exists super_users;
352select user_id as id
353into temp table super_users
354from survey
355where (
356 (q1 <> '0' and q1 <> '1')
357 and
358 (q7 <> '0' and q7 <> '1' and q7 <> '2')
359 )
360-- 2,429
361
362-- And let's create a list of IDs for everyone else
363drop table if exists other_users;
364select id as id
365into temp table other_users
366from users
367where id not in (select id from super_users);
368-- 24,838
369
370--------------------------------------------------
371--------------------------------------------------
372
373-- Now let's examine our events data each in segment
374
375select * from events limit 10;
376
377--------------------
378-- event code ref --
379--------------------
380
381-- bookmark_status: 8
382-- bookmark_create: 9
383-- bookmark_choose: 10
384-- bookmark_modify: 11
385-- num_tabs: 26
386
387-- Put them in their own table
388
389drop table if exists event_codes;
390select distinct event_code as code
391into temp table event_codes
392from events
393where (event_code = 8 or event_code = 9 or event_code = 10 or event_code = 11 or event_code = 26);
394select * from event_codes;
395
396-- number of bookmarks created: bookmark_status.data1
397-- number of bookmarks chosen: bookmark_choose
398-- number of tabs: num_tabs.data2
399
400-- Compare event/user counts across the two groups
401
402with super as (
403 with su as (select count(*) as "total" from super_users)
404 select
405 event_code,
406 count(*) as "su_total",
407 round(count(*) / avg(su.total), 2) as "su_capita"
408 from events, su
409 where
410 event_code in (select code from event_codes)
411 and
412 user_id in (select id from super_users)
413 group by 1
414), other as (
415 with ot as (select count(*) as "total" from other_users)
416 select
417 event_code,
418 count(*) as "ot_total",
419 round(count(*) / avg(ot.total), 2) as "ot_capita"
420 from events, ot
421 where
422 event_code in (select code from event_codes)
423 and
424 user_id in (select id from other_users)
425 group by 1
426)
427select
428 *
429from super left join other using (event_code)
430order by 1;
431
432--------------------
433-- sanity checks --
434--------------------
435
436-- Super users: 2,429
437-- Other users: 24,838
438
439-- How many unique users in the events table?
440select count(distinct user_id) from events;
441-- 14,718
442
443-- How many users have at least 1 event recorded?
444select count(distinct user_id) from events where user_id in
445(select id from super_users);
446-- Super: 1,430 (59%)
447-- Other: 13,288 (53%)
448
449-- At least 1 bookmark_status event?
450select count(distinct user_id)
451from events
452where
453 user_id in (select id from super_users)
454 and event_code = 8;
455-- Super: 874 (36%)
456-- Other: 9,721 (39%)
457
458-- At least 1 num_tabs event?
459select count(distinct user_id)
460from events
461where
462 user_id in (select id from super_users)
463 and event_code = 26;
464-- 1,412 (58%)
465-- 13,186 (53%)
466
467--------------------
468-- bookmarks --
469--------------------
470
471-- Number of bookmark_status events?
472select count(*) from events where event_code = 8;
473-- 23,758
474
475-- Show distribution of bookmarks across both segments
476select
477 user_id,
478 round(avg(split_part(data1, ' ', 1)::int))
479from events
480where
481 event_code = 8
482 and user_id in (select id from super_users)
483group by 1
484order by 2 desc;
485
486-- Summary stats on num_bookmarks for each segment?
487with super as (
488 select
489 user_id,
490 round(avg(split_part(data1, ' ', 1)::int)) as "bookmarks"
491 from events
492 where
493 event_code = 8
494 and user_id in (select id from super_users)
495 group by 1
496)
497select
498 count(bookmarks) as "users",
499 round(avg(bookmarks)) as "mean_bk",
500 round(median(bookmarks)) as "median_bk",
501 max(bookmarks) as "max_bk",
502 min(bookmarks) as "min_bk",
503 round(stddev(bookmarks)) as "stdev_bk",
504 round(variance(bookmarks)) as "var_bk"
505from super;
506
507--------------------
508-- tabs --
509--------------------
510
511select count(*) from events where event_code = 26;
512-- 452,715 events
513
514-- Show distribution
515select
516 user_id,
517 round(avg(split_part(data2, ' ', 1)::int))
518from events
519where
520 event_code = 26
521 and user_id in (select id from super_users)
522group by 1
523order by 2 desc;
524
525-- Summary stats (using max)
526with super as (
527 select
528 user_id,
529 max(split_part(data2, ' ', 1)::int) as "tabs"
530 from events
531 where
532 event_code = 26
533 and user_id in (select id from other_users)
534 group by 1
535)
536select
537 count(user_id) as "users",
538 (select count(user_id) from super where tabs >= 10) as "max_tabs_10+",
539 round((select count(user_id) from super where tabs >= 10)::numeric / count(user_id) * 100, 2) as "%max_tabs_10+",
540 (select count(user_id) from super where tabs >= 8) as "max_tabs_8+",
541 round((select count(user_id) from super where tabs >= 8)::numeric / count(user_id) * 100, 2) as "%max_tabs_8+",
542 (select count(user_id) from super where tabs >= 6) as "max_tabs_6+",
543 round((select count(user_id) from super where tabs >= 6)::numeric / count(user_id) * 100, 2) as "%max_tabs_6+",
544 (select count(user_id) from super where tabs >= 4) as "max_tabs_4+",
545 round((select count(user_id) from super where tabs >= 4)::numeric / count(user_id) * 100, 2) as "%max_tabs_4+",
546 round(avg(tabs)) as "mean_tab",
547 round(median(tabs)) as "median_tab",
548 max(tabs) as "max_tab",
549 min(tabs) as "min_tab",
550 round(stddev(tabs)) as "stdev_tab",
551 round(variance(tabs)) as "var_tab"
552from super;