CODEWITHSUNDEEP
mysqlsql
data-bite
data-bite

Reputation: 417

SQL select first 3 records from given date

I have two tables namely Customers and Transactions. There is offer that starts from the 1st of the current month.

  1. New Customers (created after 1st of this month) will get 100% cashback for their first transaction.
  2. Old Customers (created before 1st of this month) get 50% on their first and second transaction since 1st of this month.
  3. Customers in 1st point of the offer are eligible for second point as well.

Customers Table

Customer_ID   Email       Created_Date
1             [email protected]   2015-08-14 12:25:55
2             [email protected]   2016-01-23 18:16:34
.
.
n             [email protected]   2016-05-05 23:25:43

Transactions Table

Trans_ID      Customer_ID   Trans_Date            Amount
asd654qwe     2             2015-09-25 13:15:56   1200
dfg123xcv     56            2016-03-22 21:26:52   100
.
.
rty321cvb     4125          2016-05-05 08:42:06   500

I need to select only first 3 transactions of all customers after 1st of current month and then if the customer is new his first transaction is eligible for 100% cashback. New customers second and third transaction is eligible for 50% cashback. If customer is old his first and second transaction after 1st of this month is eligible for 50% cashback each.

I need to generate the report on daily basis for transaction done yesterday and share it to accounts team. SQL is not my primary task and due to shortage i need to look into it. I am doing all this manually using excel. It is very time consuming. Can anyone please let me know a query that could give me the expected results?

Expected Result

c.Email   c.Created_Date  t.Trans_ID   t.Trans_Date   t.Amount   Offer_Type
record    record          record       record         record     First
record    record          record       record         record     Repeate
record    record          record       record         record     Repeate

Upvotes: 1

Views: 724

Answers (1)

quest4truth
quest4truth

Reputation: 1140

Is this what you're looking for?

 SET @limit3 := 0, @cust := '';

 --outer query determines offer type and limits the offer to three transactions per customer
 SELECT Email, Created_Date, Trans_ID, Trans_Date, Amount
 CASE WHEN Created_Date > First_Day THEN 'First'
      ELSE 'Repeate'
 END CASE AS Offer_Type,
 @limit3 := if (@cust = Customer_ID, @limit3 + 1, 1) AS rowcount,
 @cust := Customer_ID


 --Inner query selects applicable fields, creates First_Day field, 
 --filters it to transactions done yesterday
 FROM
 (
   SELECT c.Email, c.Created_Date, t.Trans_ID, t.Trans_Date, t.Amount, c.Customer_ID
      DATE_ADD(LAST_DAY(c.Created_Date), interval 1 DAY), interval -1 MONTH) as First_Day
   FROM Customers c
   JOIN Transactions t
   ON c.Customer_ID = t.Customer_ID
   WHERE DATE(Trans_Date) = SUBDATE(NOW(), 1)
 )  AS sub

 GROUP BY c.Customer_ID
 HAVING rowcount <= 3
 ORDER BY c.Created_Date

Sort of convoluted to do in mysql

Upvotes: 1

Related Questions