I am trying to solve this question bu getting the answer as wrong. Please help
As part of HackerPay's billing analytics, a team needs a list of MVCs(Most Valued Customers ) and their total transactions in December. An MVC is a customer with 3 or more transactions in a month.
List all customers who are MVC in December. For each MVC, include the customer's name(cystomer), their transactions count , and the asum of their tranactions amount for the month.
The result should be in the following format: customer, tranacations, total. Sort the result ascending by customer.
Schema: Events table name type description dt VARCHAR(19) Transaction timestamp customer VARCHAR(64) Customer name amount DECIMAL(5,2) Transaction amount
Expected output: customer transactions total Maribel Braim 3 179.40
My code: select customer,count(customer) as transactions,sum(amount) as total from events where month(dt) = ‘DECEMBER’ group by customer order by customer
CodePudding user response:
Since it doesn't fit a comment:
select customer
, count(customer) as transactions
, sum(amount) as total
from events
where month(dt) = ‘DECEMBER’ -- or 12, depending on what month() returns for your db. Ensures you only select the correct month
group by customer -- ensures you count and sum per costumer
having count(customer) >= 3 -- ensures you only show customers with 3 or more transactions this month, after the grouping
order by customer