kaeverett

-- users since 3/31
select count(*) from users where
                            email  NOT LIKE '%test%' and email NOT LIKE '%m@v.net%' and email NOT LIKE '%avalara%'
                            and "createdAt" > '3/31/2015';


select companies.name, returns.jurisdiction, returns.begins_on from returns
join companies on returns.company_id = companies.id
where state = 'FILING';

kaeverett

-- 324 returns - market we can address now: 		CT, FL, MD, PA, WI
select companies.name, users.email, returns.updated_at, returns.jurisdiction, returns.frequency, returns.state, returns.gross_sales, returns.taxable_sales from returns
join companies on returns.company_id = companies.id
join users on companies.id = users.company_id
where returns.deleted_at ISNULL and (returns.state = 'ACCRUING' or returns.state = 'DUE')
      and users.email  NOT LIKE '%@test%' and email NOT LIKE '%m@v.net%' and email NOT LIKE '%avalara%'
      and (returns.jurisdiction = 'CT' or returns.jurisdiction = 'FL' or returns.jurisdiction = 'MD' or returns.jurisdiction = 'PA' or returns.jurisdiction = 'WI')
;

--- Paper AR, AZ, CO, DC, HI, KY, MI, MO, MS, ND, NV, OK, SC, SD, VT, WV

kaeverett

-- KPI -- this lists current state [lead, opportunity, deal].  not funnel metrics
select count(*), (companies.state) as id from companies
join users on companies.id = users.company_id
where
      email  NOT LIKE '%test.co%' and email NOT LIKE '%avalara%'
      --and companies."createdAt" > '01-01-2016'
GROUP BY companies.state;

-- LEADs by month - step 3 sets the biz address
SELECT date_trunc('month', users."createdAt") AS "Month" , count(*) AS "leads"

kaeverett

--  reminder emails for fully registered filers
select companies.name, users.email as "Email", users.first_name, users.last_name, tax_registrations.frequency, count(returns.id) as "due" from returns
JOIN companies on returns.company_id = companies.id
JOIN users on users.company_id = returns.company_id
JOIN tax_registrations on returns.tax_registration_id = tax_registrations.id
where
  email  NOT LIKE '%test.%' and email NOT LIKE '%avalara%'
  --AND tax_registrations.frequency = 'Monthly'
  AND tax_registrations.registration NOTNULL
  AND (returns.state = 'DUE' or returns.state = 'ACCRUING')

kaeverett

-- RETURN TREND - filing
SELECT date_trunc('month', returns.filed_on) AS "Month" ,
  count(case when returns.state = 'FILED' then returns.id end) AS "filed count",
  count(case when returns.state = 'FILING' then returns.id end) AS "filing count",
  count(case when returns.state = 'REJECTED' then returns.id end) AS "rejected count"
FROM returns
  join users ON returns.company_id = users.company_id
  where
   email  NOT LIKE '%test.%' and email NOT LIKE '%avalara%' AND
   (returns.filed_on > now() - interval '6 months' ) AND

kaeverett

select companies.name,
  summaries.file_count, summaries.connector_count, summaries.filed_return_count,
  returns.form_id, returns.frequency, returns.due_on, returns.state,
  returns.tax_collected as "total tax",
  count(case when items.valid = true and items.order_date >= returns.begins_on then items.id end) AS "bucketed items",
  sum(case when items.valid = true and items.order_date < returns.begins_on then items.sales_tax end) AS "swept tax",
  count(case when items.valid = true and items.order_date < returns.begins_on then items.id end) AS "swept items"
from returns
join items on returns.id = items.return_id
join users on users.company_id = items.company_id

kaeverett

select companies.name, companies.ztb_crm_id, subscriptions.* from subscriptions
JOIN companies ON subscriptions.company_id = companies.id
where
subscriptions.created_at > '02/01/2016';

kaeverett

-- stale amazon users w/ active connections: 32
   select  companies.name, users.token, users.email, users.last_login_at
   from company_connectors
   join connectors on connectors.id = company_connectors.connector_id
   join users on company_connectors.user_id = users.id
   join companies on companies.id = users.company_id
   join datasources on datasources.company_id  = users.company_id
   join summaries on companies.id = summaries.company_id
   join tax_registrations on tax_registrations.company_id = companies.id
   where

kaeverett

select
      companies.name as "company_name", to_char(companies.created_at, 'YYYY-MM') as "company_created_at",
      users.email, users.first_name, users.last_name,
       to_char(users.last_login_at, 'YYYY-MM') AS "last_login",
  tax_registrations.jurisdiction, tax_registrations.form_id, tax_registrations.frequency, tax_registrations.registration, to_char(tax_registrations.updated_at, 'YYYY-MM') as "registration_update_at",
      summaries.connector_count as "connector_count", summaries.bank_accounts,
      (case
        when summaries.file_count <= 5 then '0-5'
        when summaries.file_count > 5 and summaries.file_count <= 25 then '5-25'
        when summaries.file_count > 25  then '>25'

kaeverett

SELECT users.email, tax_registrations.form_id,tax_registrations.frequency, tax_registrations.created_at  FROM users
join tax_registrations on users.company_id = tax_registrations.company_id
join companies on users.company_id = companies.id
where
    users.email  NOT LIKE '%@test.%' and users.email NOT LIKE '%avalara%'
    and tax_registrations.id in (
      SELECT min (tax_registrations.id)
      FROM tax_registrations
      WHERE tax_registrations.created_at > '02/01/2016' and tax_registrations.created_at < '03/01/2016' 
      GROUP BY tax_registrations.company_id