CODEWITHSUNDEEP
mysqlsql
SQL_New_bee
SQL_New_bee

Reputation: 125

count of transactions by the customer in a month

Given the following the tables

User_info:

User_trascations:

calculating 30day active spenders for Jan 2020-01-01, count (customers who made at least one transaction in last 30days i.e. 2020-01-01 to 2020-01-31).

The result table should be as follows:
-------------------------------------------------------------
Date      |   Total_customers |   Customers_who_made_purchase
-------------------------------------------------------------

01-01-2020     100               20
02-01-2020     200               60
Total          300               80

How do I get the totals in the last row? that too in SQL?

I have tried this:

select
  date(User_info.date) as date 
  , count(distinct User_trascations.user_id), 
      count(distinct User_info.user_id) as conversion
from User_info join User_trascations
  on User_info.user_id = User_trascations.user_id
group by 1

Upvotes: 0

Views: 990

Answers (1)

Ivan Verges
Ivan Verges

Reputation: 641

As i said in the comments, this is an example to give you an idea about how to get the result you're looking for.

SELECT
    COALESCE(A.Date, 'Total') AS User_Date,
    COUNT(DISTINCT(B.User_Id)) AS Total_Customers,
    COUNT(DISTINCT(A.User_Id)) AS Customer_Purchases
FROM
    USER_INFO A JOIN USER_TRASCATIONS B ON
    A.User_Id = B.User_Id
GROUP BY
    ROLLUP(A.Date)

The Coalesce function it's like an if/else. When value is null (A.Date value), then it will return the passed string ('Total'). This will be the text you see at the end of the result (the summary).

The Rollup function works with the Group By to define result groups and get the resume at the last row.

Upvotes: 1

Related Questions