0

I have a table with following columns:

Amount;
Discount;
Timestamp (UTC) with two years 2016/2017;
Order_ID;
Customer_ID

Now I want to get a list of unique customers along with new customers (who have never transacted before) per month between Jan-2016 and Feb-2017 but as per my local time zone i.e. IST and not UTC

I have written the below code to get unique customers per month but not able to understand how to incorporate count(new customers) and change time zone simultaneously

SELECT SUM(Amount) AS total, 
       SUM(discount) AS tdisc, 
       YEAR(timestamp) AS yyyy, 
       MONTH(timestamp) AS mm, 
       COUNT(order_id), 
       COUNT(DISTINCT(customer_id)) AS distcid 
FROM table t 
GROUP BY YEAR(timestamp), 
         MONTH(timestamp) 
HAVING YEAR(timestamp) >= 2016 AND 
       YEAR(timestamp) < 2017 
ORDER BY timestamp;
KIKO Software
  • 15,283
  • 3
  • 18
  • 33
Rakshit
  • 11
  • 2
  • For converting the time zone see: https://stackoverflow.com/a/15018103/3986005 – KIKO Software Aug 03 '19 at 08:28
  • To know if a customer is new, you need to know if an order is the first order of a customer. For this is would be useful if `Order_ID` contains an integer that auto increases with time. In other words: Orders are numbered chronologically. Is that the case here? – KIKO Software Aug 03 '19 at 08:36
  • @KIKOSoftware Thank you for directing me to the timezone link. And no orders are not numbered chronologically in my case – Rakshit Aug 03 '19 at 09:04
  • Are you saying the timestamp does not represent a chronological order? – P.Salmon Aug 03 '19 at 10:47

0 Answers0