Hi,
I’m trying to create a Week over Week active user count summary report / table aggregated by month. I have one table for June 2017 and one table for May 2016 which I need to join together in order to. The date timestamp is created_utc which iis a UNIX time stamp which I can figure out to transform into a human readable format and from there extract the week of the year value so 1 through 52. This things I need assistance on are...
- Number the weeks just by values of 1 through 4. So, week 1 for June, Week 1 for May, Week 2 for June week 2 for May as so on.
- Joining the tables based by those week 1 through 4 values
- Pivoting the table and adding a WOW Change variable.
I'd like the final table to look like this:
Month | June_count | May_count | WOW_Change |
---|---|---|---|
Week_1 | 5 | 8 | 0.6 |
Week_2 | 2 | 1 | -0.5 |
Week_3 | 10 | 5 | -0.5 |
Week_4 | 3 | 6 | 1 |
Below is some sample data as well as the code I've started. Any help will be greatly appreciated! Thanks!
CREATE TABLE 2017_06
(created_utc
int, id
varchar(6))
;
INSERT INTO 2017_06
(created_utc
, userid
)
VALUES
(1496354167, '6eq4xf'),
(1496362973, '6eqzz3'),
(1496431934, '6ewlm8'),
(1496870877, '6fwied'),
(1496778080, '6fo79k'),
(1496933893, '6g1gcg'),
(1497154559, '6gjkid'),
(1497618561, '6hmeud'),
(1497377349, '6h1osm'),
(1497221017, '6god73'),
(1497731470, '6hvmic'),
(1497273130, '6gs4ay'),
(1498080798, '6ioz8q'),
(1497769316, '6hyer4'),
(1497415729, '6h5cgu'),
(1497978764, '6iffwq')
;
CREATE TABLE 2017_05
(created_utc
int, id
varchar(6))
;
INSERT INTO 2017_05
(created_utc
, userid
)
VALUES
(1493729491, '68sx7k'),
(1493646801, '68m2s2'),
(1493747285, '68uohf'),
(1493664087, '68ntss'),
(1493690759, '68qe5k'),
(1493829196, '691fy9'),
(1493646344, '68m1dv'),
(1494166859, '69rhkl'),
(1493883023, '6963qb'),
(1494362328, '6a83wv'),
(1494525998, '6alv6c'),
(1493945230, '69bkhb'),
(1494050355, '69jqtz'),
(1494418011, '6accd0'),
(1494425781, '6ad0xm'),
(1494024697, '69hx2z'),
(1494586576, '6aql9y')
;
#standardSQL
SELECT created_utc,
DATE(TIMESTAMP_SECONDS(created_utc)) as event_date,
CAST(EXTRACT(WEEK FROM TIMESTAMP_SECONDS(created_utc)) AS STRING) AS week_number,
COUNT(distinct userid) as user_count
FROM 2017_06
SELECT created_utc,
DATE(TIMESTAMP_SECONDS(created_utc)) as event_date,
CAST(EXTRACT(WEEK FROM TIMESTAMP_SECONDS(created_utc)) AS STRING) AS week_number,
COUNT(distinct userid) as user_count
FROM 2017_05