Only if the used_at and expired_at is null the credit is counted as remaining credit

user table:

user_credit table:

user_credit_history table:

The query I use is:

select u.id, u.email, u.name, uc.id as credit_id, uc.added_at as purchase_date, uc.credits_added, count(uch.credit_id) as credits_remaining 
from user u 
left join user_credit uc on uc.user_id = u.id 
left join user_credit_history uch on uch.credit_id = uc. 
where uc.type='CREDIT' 
    and uc.credits_added >= 100 
    and (uch.used_at is null and uch.expired_at is null) 
group by uch.credit_id 
order by uc.added_at desc;

But this query is giving me all the purchase details of each user but I only need latest one.

CodePudding user response：

instead of joining the table "user_credit_history" you can use a subselect to get the 1 row you want per user by using order by in your subselect for the field "used_at".

CodePudding user response：

1. you can use :-

SELECT 
    u.id,
    u.email,
    u.name,
    uc.id AS credit_id,
    uc.added_at AS purchase_date,
    uc.credits_added,
    COUNT(uch.credit_id) AS credits_remaining
FROM
    user u
        LEFT JOIN
    (SELECT 
        user_id, id, added_at, credits_added
    FROM
        user_credit
    GROUP BY user_id
    HAVING MAX(purchase_date)) AS uc ON uc.user_id = u.id
        LEFT JOIN
    user_credit_history uch ON uch.credit_id = uc.id
WHERE
    uc.type = 'CREDIT'
        AND uc.credits_added >= 100
        AND (uch.used_at IS NULL
        AND uch.expired_at IS NULL)
GROUP BY uch.credit_id
ORDER BY uc.added_at DESC;

CodePudding user response：

It is difficult to understand because there is no expected results... This query calculated for each user its latest purchase and fetches it and calculates the remaining credits for the credit_id of the latest purchase of the user and I've added the 100 filter:

select u.id, u.email, u.name, uc.id as credit_id, uc.added_at as purchase_date, uc.credits_added, count(credit_id) as credits_remaining
from
   (
      select user_id, max(added_at) as latest_purchase
      from user_credit
      group by user_id
   ) up
   inner join user_credit uc on uc.user_id=up.user_id and uc.added_at=up.latest_purchase
   inner join users u on u.id=uc.user_id
   inner join user_credit_history uch on uch.credit_id=uc.id
where uc.type='CREDIT' and 
      uc.credits_added >= 100 and 
      used_at is null and expired_at is null
group by uch.credit_id, u.id, u.email, u.name, uc.id as credit_id, uc.added_at as purchase_date, uc.credits_added