{"action":"created","comment":{"url":"https://api.github.com/repos/analyst-collective/models/pulls/comments/57489265","id":57489265,"diff_hunk":"@@ -0,0 +1,132 @@\n+create or replace view {{env.schema}}.stripe_invoices as (\n+\n+  select\n+    customer,\n+    date,\n+    forgiven,\n+    subscription as subscription_id,\n+    paid,\n+    total,\n+    period_start,\n+    period_end\n+  from\n+    demo_data.stripe_invoices\n+\n+);\n+\n+create or replace view {{env.schema}}.stripe_invoices_cleaned as (\n+\n+  select\n+    customer,\n+    timestamp 'epoch' + date * interval '1 Second' as date,\n+    forgiven,\n+    subscription_id,\n+    paid,\n+    total,\n+    timestamp 'epoch' + period_start * interval '1 Second' as period_start,\n+    timestamp 'epoch' + period_end * interval '1 Second' as period_end\n+  from\n+    {{env.schema}}.stripe_invoices\n+\n+);\n+\n+create or replace view {{env.schema}}.stripe_subscriptions as (\n+\n+  select\n+    id,\n+    plan__id as plan_id\n+  from\n+    demo_data.stripe_subscriptions\n+\n+);\n+\n+create or replace view {{env.schema}}.stripe_plans as (\n+\n+  select\n+    id,\n+    interval\n+  from\n+    demo_data.stripe_plans\n+\n+);\n+\n+\n+\n+\n+create or replace view {{env.schema}}.stripe_invoices_transformed as (\n+\n+  with invoices as (\n+\n+    select *\n+    from {{env.schema}}.stripe_invoices_cleaned\n+    where paid is true\n+      and forgiven is false\n+\n+  ), d1 as (\n+\n+    select (min(period_start) over () + row_number() over ())::date as date_day\n+    from invoices\n+\n+  ), dates as (\n+\n+    select distinct date_trunc('month', date_day)::date as date_month\n+    from d1\n+    where date_day <= current_date\n+\n+  ), customers as (\n+\n+    select customer, min(period_start) as active_from, max(period_end) as active_to\n+    from invoices\n+    where period_start <= current_date\n+    group by 1\n+\n+  ), customer_dates as (\n+\n+    select date_month, customer\n+    from dates d\n+      inner join customers c\n+        on d.date_month >= date_trunc('month', c.active_from)\n+          and d.date_month < date_trunc('month', c.active_to)\n+\n+  ), data as (\n+\n+    select date_month, d.customer, period_start, period_end,\n+      \"interval\" as period,\n+      case \"interval\"\n+        when 'yearly'\n+          then coalesce(i.total, 0)::float / 12 / 100\n+        else\n+          coalesce(i.total, 0)::float / 100\n+      end as total,\n+      first_value(date_month)","path":"model/stripe/model.sql","position":101,"original_position":101,"commit_id":"8b19da93de08ecce16a619312231170258a96e93","original_commit_id":"8b19da93de08ecce16a619312231170258a96e93","user":{"login":"jthandy","id":2341841,"avatar_url":"https://avatars.githubusercontent.com/u/2341841?v=3","gravatar_id":"","url":"https://api.github.com/users/jthandy","html_url":"https://github.com/jthandy","followers_url":"https://api.github.com/users/jthandy/followers","following_url":"https://api.github.com/users/jthandy/following{/other_user}","gists_url":"https://api.github.com/users/jthandy/gists{/gist_id}","starred_url":"https://api.github.com/users/jthandy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jthandy/subscriptions","organizations_url":"https://api.github.com/users/jthandy/orgs","repos_url":"https://api.github.com/users/jthandy/repos","events_url":"https://api.github.com/users/jthandy/events{/privacy}","received_events_url":"https://api.github.com/users/jthandy/received_events","type":"User","site_admin":false},"body":"updated this. good call.","created_at":"2016-03-25T20:48:00Z","updated_at":"2016-03-25T20:48:00Z","html_url":"https://github.com/analyst-collective/models/pull/28#discussion_r57489265","pull_request_url":"https://api.github.com/repos/analyst-collective/models/pulls/28","_links":{"self":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/comments/57489265"},"html":{"href":"https://github.com/analyst-collective/models/pull/28#discussion_r57489265"},"pull_request":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/28"}}},"pull_request":{"url":"https://api.github.com/repos/analyst-collective/models/pulls/28","id":63712096,"html_url":"https://github.com/analyst-collective/models/pull/28","diff_url":"https://github.com/analyst-collective/models/pull/28.diff","patch_url":"https://github.com/analyst-collective/models/pull/28.patch","issue_url":"https://api.github.com/repos/analyst-collective/models/issues/28","number":28,"state":"open","locked":false,"title":"Models/stripe","user":{"login":"jthandy","id":2341841,"avatar_url":"https://avatars.githubusercontent.com/u/2341841?v=3","gravatar_id":"","url":"https://api.github.com/users/jthandy","html_url":"https://github.com/jthandy","followers_url":"https://api.github.com/users/jthandy/followers","following_url":"https://api.github.com/users/jthandy/following{/other_user}","gists_url":"https://api.github.com/users/jthandy/gists{/gist_id}","starred_url":"https://api.github.com/users/jthandy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jthandy/subscriptions","organizations_url":"https://api.github.com/users/jthandy/orgs","repos_url":"https://api.github.com/users/jthandy/repos","events_url":"https://api.github.com/users/jthandy/events{/privacy}","received_events_url":"https://api.github.com/users/jthandy/received_events","type":"User","site_admin":false},"body":"This includes models and analysis to perform basic subscription MRR analysis for Stripe.","created_at":"2016-03-22T12:31:28Z","updated_at":"2016-03-25T20:48:00Z","closed_at":null,"merged_at":null,"merge_commit_sha":"263e503780ef4c3d9dae58fb463bebf3f40611ee","assignee":{"login":"3mei","id":11296522,"avatar_url":"https://avatars.githubusercontent.com/u/11296522?v=3","gravatar_id":"","url":"https://api.github.com/users/3mei","html_url":"https://github.com/3mei","followers_url":"https://api.github.com/users/3mei/followers","following_url":"https://api.github.com/users/3mei/following{/other_user}","gists_url":"https://api.github.com/users/3mei/gists{/gist_id}","starred_url":"https://api.github.com/users/3mei/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/3mei/subscriptions","organizations_url":"https://api.github.com/users/3mei/orgs","repos_url":"https://api.github.com/users/3mei/repos","events_url":"https://api.github.com/users/3mei/events{/privacy}","received_events_url":"https://api.github.com/users/3mei/received_events","type":"User","site_admin":false},"milestone":null,"commits_url":"https://api.github.com/repos/analyst-collective/models/pulls/28/commits","review_comments_url":"https://api.github.com/repos/analyst-collective/models/pulls/28/comments","review_comment_url":"https://api.github.com/repos/analyst-collective/models/pulls/comments{/number}","comments_url":"https://api.github.com/repos/analyst-collective/models/issues/28/comments","statuses_url":"https://api.github.com/repos/analyst-collective/models/statuses/8b19da93de08ecce16a619312231170258a96e93","head":{"label":"analyst-collective:models/stripe","ref":"models/stripe","sha":"8b19da93de08ecce16a619312231170258a96e93","user":{"login":"analyst-collective","id":17324116,"avatar_url":"https://avatars.githubusercontent.com/u/17324116?v=3","gravatar_id":"","url":"https://api.github.com/users/analyst-collective","html_url":"https://github.com/analyst-collective","followers_url":"https://api.github.com/users/analyst-collective/followers","following_url":"https://api.github.com/users/analyst-collective/following{/other_user}","gists_url":"https://api.github.com/users/analyst-collective/gists{/gist_id}","starred_url":"https://api.github.com/users/analyst-collective/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/analyst-collective/subscriptions","organizations_url":"https://api.github.com/users/analyst-collective/orgs","repos_url":"https://api.github.com/users/analyst-collective/repos","events_url":"https://api.github.com/users/analyst-collective/events{/privacy}","received_events_url":"https://api.github.com/users/analyst-collective/received_events","type":"Organization","site_admin":false},"repo":{"id":52040684,"name":"models","full_name":"analyst-collective/models","owner":{"login":"analyst-collective","id":17324116,"avatar_url":"https://avatars.githubusercontent.com/u/17324116?v=3","gravatar_id":"","url":"https://api.github.com/users/analyst-collective","html_url":"https://github.com/analyst-collective","followers_url":"https://api.github.com/users/analyst-collective/followers","following_url":"https://api.github.com/users/analyst-collective/following{/other_user}","gists_url":"https://api.github.com/users/analyst-collective/gists{/gist_id}","starred_url":"https://api.github.com/users/analyst-collective/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/analyst-collective/subscriptions","organizations_url":"https://api.github.com/users/analyst-collective/orgs","repos_url":"https://api.github.com/users/analyst-collective/repos","events_url":"https://api.github.com/users/analyst-collective/events{/privacy}","received_events_url":"https://api.github.com/users/analyst-collective/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/analyst-collective/models","description":"","fork":false,"url":"https://api.github.com/repos/analyst-collective/models","forks_url":"https://api.github.com/repos/analyst-collective/models/forks","keys_url":"https://api.github.com/repos/analyst-collective/models/keys{/key_id}","collaborators_url":"https://api.github.com/repos/analyst-collective/models/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/analyst-collective/models/teams","hooks_url":"https://api.github.com/repos/analyst-collective/models/hooks","issue_events_url":"https://api.github.com/repos/analyst-collective/models/issues/events{/number}","events_url":"https://api.github.com/repos/analyst-collective/models/events","assignees_url":"https://api.github.com/repos/analyst-collective/models/assignees{/user}","branches_url":"https://api.github.com/repos/analyst-collective/models/branches{/branch}","tags_url":"https://api.github.com/repos/analyst-collective/models/tags","blobs_url":"https://api.github.com/repos/analyst-collective/models/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/analyst-collective/models/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/analyst-collective/models/git/refs{/sha}","trees_url":"https://api.github.com/repos/analyst-collective/models/git/trees{/sha}","statuses_url":"https://api.github.com/repos/analyst-collective/models/statuses/{sha}","languages_url":"https://api.github.com/repos/analyst-collective/models/languages","stargazers_url":"https://api.github.com/repos/analyst-collective/models/stargazers","contributors_url":"https://api.github.com/repos/analyst-collective/models/contributors","subscribers_url":"https://api.github.com/repos/analyst-collective/models/subscribers","subscription_url":"https://api.github.com/repos/analyst-collective/models/subscription","commits_url":"https://api.github.com/repos/analyst-collective/models/commits{/sha}","git_commits_url":"https://api.github.com/repos/analyst-collective/models/git/commits{/sha}","comments_url":"https://api.github.com/repos/analyst-collective/models/comments{/number}","issue_comment_url":"https://api.github.com/repos/analyst-collective/models/issues/comments{/number}","contents_url":"https://api.github.com/repos/analyst-collective/models/contents/{+path}","compare_url":"https://api.github.com/repos/analyst-collective/models/compare/{base}...{head}","merges_url":"https://api.github.com/repos/analyst-collective/models/merges","archive_url":"https://api.github.com/repos/analyst-collective/models/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/analyst-collective/models/downloads","issues_url":"https://api.github.com/repos/analyst-collective/models/issues{/number}","pulls_url":"https://api.github.com/repos/analyst-collective/models/pulls{/number}","milestones_url":"https://api.github.com/repos/analyst-collective/models/milestones{/number}","notifications_url":"https://api.github.com/repos/analyst-collective/models/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/analyst-collective/models/labels{/name}","releases_url":"https://api.github.com/repos/analyst-collective/models/releases{/id}","deployments_url":"https://api.github.com/repos/analyst-collective/models/deployments","created_at":"2016-02-18T21:38:59Z","updated_at":"2016-03-17T15:17:21Z","pushed_at":"2016-03-24T20:40:23Z","git_url":"git://github.com/analyst-collective/models.git","ssh_url":"git@github.com:analyst-collective/models.git","clone_url":"https://github.com/analyst-collective/models.git","svn_url":"https://github.com/analyst-collective/models","homepage":null,"size":170,"stargazers_count":1,"watchers_count":1,"language":null,"has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":5,"mirror_url":null,"open_issues_count":8,"forks":5,"open_issues":8,"watchers":1,"default_branch":"master"}},"base":{"label":"analyst-collective:master","ref":"master","sha":"fe770d5e1b5f4611dfe5153085e82e876e4b8468","user":{"login":"analyst-collective","id":17324116,"avatar_url":"https://avatars.githubusercontent.com/u/17324116?v=3","gravatar_id":"","url":"https://api.github.com/users/analyst-collective","html_url":"https://github.com/analyst-collective","followers_url":"https://api.github.com/users/analyst-collective/followers","following_url":"https://api.github.com/users/analyst-collective/following{/other_user}","gists_url":"https://api.github.com/users/analyst-collective/gists{/gist_id}","starred_url":"https://api.github.com/users/analyst-collective/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/analyst-collective/subscriptions","organizations_url":"https://api.github.com/users/analyst-collective/orgs","repos_url":"https://api.github.com/users/analyst-collective/repos","events_url":"https://api.github.com/users/analyst-collective/events{/privacy}","received_events_url":"https://api.github.com/users/analyst-collective/received_events","type":"Organization","site_admin":false},"repo":{"id":52040684,"name":"models","full_name":"analyst-collective/models","owner":{"login":"analyst-collective","id":17324116,"avatar_url":"https://avatars.githubusercontent.com/u/17324116?v=3","gravatar_id":"","url":"https://api.github.com/users/analyst-collective","html_url":"https://github.com/analyst-collective","followers_url":"https://api.github.com/users/analyst-collective/followers","following_url":"https://api.github.com/users/analyst-collective/following{/other_user}","gists_url":"https://api.github.com/users/analyst-collective/gists{/gist_id}","starred_url":"https://api.github.com/users/analyst-collective/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/analyst-collective/subscriptions","organizations_url":"https://api.github.com/users/analyst-collective/orgs","repos_url":"https://api.github.com/users/analyst-collective/repos","events_url":"https://api.github.com/users/analyst-collective/events{/privacy}","received_events_url":"https://api.github.com/users/analyst-collective/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/analyst-collective/models","description":"","fork":false,"url":"https://api.github.com/repos/analyst-collective/models","forks_url":"https://api.github.com/repos/analyst-collective/models/forks","keys_url":"https://api.github.com/repos/analyst-collective/models/keys{/key_id}","collaborators_url":"https://api.github.com/repos/analyst-collective/models/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/analyst-collective/models/teams","hooks_url":"https://api.github.com/repos/analyst-collective/models/hooks","issue_events_url":"https://api.github.com/repos/analyst-collective/models/issues/events{/number}","events_url":"https://api.github.com/repos/analyst-collective/models/events","assignees_url":"https://api.github.com/repos/analyst-collective/models/assignees{/user}","branches_url":"https://api.github.com/repos/analyst-collective/models/branches{/branch}","tags_url":"https://api.github.com/repos/analyst-collective/models/tags","blobs_url":"https://api.github.com/repos/analyst-collective/models/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/analyst-collective/models/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/analyst-collective/models/git/refs{/sha}","trees_url":"https://api.github.com/repos/analyst-collective/models/git/trees{/sha}","statuses_url":"https://api.github.com/repos/analyst-collective/models/statuses/{sha}","languages_url":"https://api.github.com/repos/analyst-collective/models/languages","stargazers_url":"https://api.github.com/repos/analyst-collective/models/stargazers","contributors_url":"https://api.github.com/repos/analyst-collective/models/contributors","subscribers_url":"https://api.github.com/repos/analyst-collective/models/subscribers","subscription_url":"https://api.github.com/repos/analyst-collective/models/subscription","commits_url":"https://api.github.com/repos/analyst-collective/models/commits{/sha}","git_commits_url":"https://api.github.com/repos/analyst-collective/models/git/commits{/sha}","comments_url":"https://api.github.com/repos/analyst-collective/models/comments{/number}","issue_comment_url":"https://api.github.com/repos/analyst-collective/models/issues/comments{/number}","contents_url":"https://api.github.com/repos/analyst-collective/models/contents/{+path}","compare_url":"https://api.github.com/repos/analyst-collective/models/compare/{base}...{head}","merges_url":"https://api.github.com/repos/analyst-collective/models/merges","archive_url":"https://api.github.com/repos/analyst-collective/models/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/analyst-collective/models/downloads","issues_url":"https://api.github.com/repos/analyst-collective/models/issues{/number}","pulls_url":"https://api.github.com/repos/analyst-collective/models/pulls{/number}","milestones_url":"https://api.github.com/repos/analyst-collective/models/milestones{/number}","notifications_url":"https://api.github.com/repos/analyst-collective/models/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/analyst-collective/models/labels{/name}","releases_url":"https://api.github.com/repos/analyst-collective/models/releases{/id}","deployments_url":"https://api.github.com/repos/analyst-collective/models/deployments","created_at":"2016-02-18T21:38:59Z","updated_at":"2016-03-17T15:17:21Z","pushed_at":"2016-03-24T20:40:23Z","git_url":"git://github.com/analyst-collective/models.git","ssh_url":"git@github.com:analyst-collective/models.git","clone_url":"https://github.com/analyst-collective/models.git","svn_url":"https://github.com/analyst-collective/models","homepage":null,"size":170,"stargazers_count":1,"watchers_count":1,"language":null,"has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":5,"mirror_url":null,"open_issues_count":8,"forks":5,"open_issues":8,"watchers":1,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/28"},"html":{"href":"https://github.com/analyst-collective/models/pull/28"},"issue":{"href":"https://api.github.com/repos/analyst-collective/models/issues/28"},"comments":{"href":"https://api.github.com/repos/analyst-collective/models/issues/28/comments"},"review_comments":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/28/comments"},"review_comment":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/28/commits"},"statuses":{"href":"https://api.github.com/repos/analyst-collective/models/statuses/8b19da93de08ecce16a619312231170258a96e93"}}}}