-- usage report

select connector_name, count(connector_name)

FROM   dblink('dbname=trustfilestorage user=deploy password=4BFkh59jDd9YCSu',
                        'SELECT  account_id, company_name, connector_name from company_daily_usage
                        where
                          created_at > date_trunc(''MONTH'',now())::DATE
                          and usage = ''CONNECTOR_ACTIVE''
                          GROUP BY connector_name, account_id, company_name
                  ')
            AS   usage(account_id INT, company_name TEXT, connector_name TEXT)
JOIN merchants on merchants.account_id = usage.account_id
LEFT OUTER JOIN subscriptions on subscriptions.account_id = merchants.account_id
where  email  NOT LIKE '%test.%' and email NOT LIKE '%avalara%' and email NOT LIKE '%sush.io%'
--and subscriptions.expired_at ISNULL
--and subscriptions.cancelled_at ISNULL
--and subscriptions.revoked_at ISNULL
--and subscriptions.created_at > date_trunc('MONTH',now())::DATE
GROUP BY  usage.connector_name
ORDER BY usage.connector_name
;