logo
down
shadow

Query to find the number of paying customers and churned customers?


Query to find the number of paying customers and churned customers?

By : Daniel Montgomery
Date : November 22 2020, 02:59 PM
To fix this issue Not sure I understood this right: for every month you want to know how many customer started paying for their first user and how many customers stopped paying for their last user?
Solution seems rather complicated but maybe it's not that easy after all.
code :
with months as 
(
    select * from 
    generate_series('2014-06-01', now() at time zone 'utc', interval '1 month') as month
    cross join paid_users
)
, sums as
(
    select month, payor_id, joiners, leavers, sum(net) over (partition by payor_id order by month) 
    from
    (
        select month, payor_id, joiners, leavers, coalesce(joiners,0) - coalesce(leavers, 0) as net
        from
        (
            select payor_id, month, count(*) as joiners
            from months
            where payment_start_date >= month
            and payment_start_date < month + interval '1 month'
            group by month, payor_id
        ) as t
        full join
        (
            select payor_id, month, count(*) as leavers
            from months
            where payment_stop_date >= month
            and payment_stop_date < month + interval '1 month'
            group by month, payor_id
        ) as u
        using (month, payor_id)
    ) as v
)

select * from sums
order by payor_id, sum
        month        | payor_id | joiners | leavers | sum 
---------------------+----------+---------+---------+-----
 2014-06-01 00:00:00 |     1725 |       1 |         |   1
 2014-06-01 00:00:00 |     1929 |       1 |         |   1
 2015-10-01 00:00:00 |     1929 |         |       1 |   0
 2014-06-01 00:00:00 |     1986 |       1 |         |   1
 2014-11-01 00:00:00 |     3453 |       2 |         |   2
 2014-12-01 00:00:00 |     3453 |         |       2 |   0
 2015-01-01 00:00:00 |     3453 |       1 |         |   1
 2015-03-01 00:00:00 |     3453 |       1 |         |   2
 2015-04-01 00:00:00 |     3453 |       2 |       1 |   3
 2015-05-01 00:00:00 |     3453 |         |       1 |   2
 2015-06-01 00:00:00 |     3453 |         |       1 |   1
 2015-10-01 00:00:00 |     3453 |       1 |         |   2
 2015-07-01 00:00:00 |     6499 |       1 |         |   1
 2015-08-01 00:00:00 |     6499 |       3 |         |   4
 2015-10-01 00:00:00 |     6499 |         |       1 |   3
 2015-11-01 00:00:00 |     6499 |         |       1 |   2
select month, new, churned from
(
    (
        select month, count(*) as churned
        from sums
        where sum = 0
        group by month
    ) as l
    full join
    (
        select month, count(*) as new
        from (
            select month, payor_id, sum, coalesce(lag(sum) over (partition by payor_id order by month), 0) as prev_sum
            from sums
            order by payor_id, month
        ) as t
        where prev_sum = 0 and sum > 0
        group by month
    ) as r
    using (month)
)
order by month
        month        | new | churned 
---------------------+-----+---------
 2014-06-01 00:00:00 |   3 |        
 2014-11-01 00:00:00 |   1 |        
 2014-12-01 00:00:00 |     |       1
 2015-01-01 00:00:00 |   1 |        
 2015-07-01 00:00:00 |   1 |        
 2015-10-01 00:00:00 |     |       1


Share : facebook icon twitter icon
MySQL - Find Orders placed by repeat customers vs. new customers over time

MySQL - Find Orders placed by repeat customers vs. new customers over time


By : user3167502
Date : March 29 2020, 07:55 AM
may help you . One approach to retrieving the specified result would be to use a correlated subquery in the SELECT list. This assumes that the customer identifier is customers_email_address, and that date_purchased is a DATETIME or TIMESTAMP (or other canonical format), and that there are no duplicated values for the same customer (that is, the customer doesn't have two or more orders with the same date_purchased value.)
code :
SELECT s.orders_id
     , s.customers_email_address
     , s.date_purchased 
     , ( SELECT COUNT(1)
           FROM orders p
          WHERE p.customers_email_address = s.customers_email_address
            AND p.date_purchased          < s.date_purchased
       ) AS previous_order_count
  FROM orders s
 ORDER
    BY s.customers_email_address
     , s.date_purchased
ON orders (customers_email_address, date_purchased, orders_id)
SELECT s.orders_id
     , s.customers_email_address
     , s.date_purchased
     , COUNT(p.orders_id)
  FROM orders s
  JOIN orders p
    ON p.customers_email_address = s.customers_email_address
   AND p.date_purchased         <= s.date_purchased
 GROUP
    BY s.customers_email_address
     , s.date_purchased
     , s.orders_id
 ORDER
    BY s.customers_email_address
     , s.date_purchased
     , s.orders_id
... ON orders (customers_email_address, orders_id, date_purchased)
SELECT s.orders_id
     , s.customers_email_address
     , s.date_purchased
     , COUNT(p.orders_id)
  FROM orders s
  JOIN orders p
    ON p.customers_email_address = s.customers_email_address
   AND p.orders_id              <= s.orders_id
 GROUP
    BY s.customers_email_address
     , s.orders_id
 ORDER
    BY s.customers_email_address
     , s.orders_id
Which payment API allows taking payments from a set of customers and paying out to another set of customers?

Which payment API allows taking payments from a set of customers and paying out to another set of customers?


By : Sergio Rocha
Date : March 29 2020, 07:55 AM
it fixes the issue If they're being paid for services that they have performed for the customer, then Stripe and Braintree both offer APIs for Marketplaces. Stripe is called Stripe Connect, and Braintree is called Marketplaces. The key thing to note for these APIs is that you must specify who the person receiving the funds is when you charge the customer. Neither platform supports blanket transferring of funds from your "platform" to each merchant as that falls under "Money Transmission". Instead, you'll end up creating accounts for each merchant (and you'll be required to ask for information like address, tax id, bank account info etc), and the charge from the customer goes directly to their account (with you potentially taking a cut from the transaction).
Power BI - DAX Measure to calculate churned and reactivated customers in the current period. Incorrect total

Power BI - DAX Measure to calculate churned and reactivated customers in the current period. Incorrect total


By : Prasanna Venkatesh
Date : March 29 2020, 07:55 AM
wish of those help First, you need a Dates table with no relationship to your SalesData table to use as a slicer. The following works well enough for purposes here:
code :
Dates = CALENDAR( DATE( 2018, 1, 1 ), DATE( 2019, 12, 31 ) )
ChurnedInPeriod =
VAR MaxDate = MAX ( Dates[Date] )
VAR MinDate = MIN ( Dates[Date] )
VAR CustomerLastDate = CALCULATE ( MAX ( SalesData[Date] ), SalesData[Date] <= MaxDate )
VAR DaysPassed = MaxDate - CustomerLastDate
VAR PeriodLength = MaxDate - MinDate
RETURN
    IF ( DaysPassed >= 90 && DaysPassed <= 90 + PeriodLength, 1, 0 )
ChurnedCount = SUMX ( VALUES ( SalesData[Customer ID] ), [ChurnedInPeriod] )
sql query to find Which country has the maximum number of suppliers and customers

sql query to find Which country has the maximum number of suppliers and customers


By : OneProgrammer
Date : March 29 2020, 07:55 AM
I hope this helps you . To get the maximum times a single country is included in both tables as a single value, union them, then group them:
How would I query how many customers hit a page even if they weren't customers at the time?

How would I query how many customers hit a page even if they weren't customers at the time?


By : Bambuko
Date : March 29 2020, 07:55 AM
will help you You'd use a funnel to calculate this retroactively, similar to how you would do ad attribution.
Related Posts Related Posts :
  • Truncating display by default in postgres psql select statements
  • How do I avoid listing all the table columns in a PostgreSQL returns statement?
  • update values in a string to another table
  • psql displaying ansi colored text
  • Continue statement in plpgsql doesn't work
  • StrongLoop query/stored procedure with Postgres?
  • Joining with set-returning function (SRF) and access columns in SQLAlchemy
  • How to keep looping even error happend?
  • Get default serial value after INSERT inside PL/pgSQL
  • PostgreSQL using UUID vs Text as primary key
  • Can I have times greater than 24 hours in postrgres
  • Why doesn't this function actually update anything?
  • How to batch create index on 1000 tables
  • Coalescing date ranges in postgres to eliminate overlaps
  • Which way to run PostgreSQL in Docker?
  • Import osm data in Docker postgresql
  • postgresql what is the best way to export specific column from specific table from a DB to another
  • PostgreSql - Having trouble displaying my table using perform statment in my function
  • Postgres Full Text Search customize tsvector conversion
  • What command should I use to make my void function display a paticular table using a variable from the function in the w
  • What are the consequences of not ending a database transaction?
  • CakePHP 3: Migration doesn't save update data
  • Inserting into postgres database
  • Load raster to PostgreSQL
  • How to return the serial primary key from a db insert to use for another db insert
  • Postgres Connection Issues with Heroku in Tornado
  • How to persist data using postgres docker image?
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co