Home > database >  Billing Analytics Customer Report Hackerrank interview question
Billing Analytics Customer Report Hackerrank interview question

Time:06-27

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
  •  Tags:  
  • sql
  • Related