Question 20077 in Mysql Question posted in
The official MySQL documentation can be found here.

Set status based on date differences – Mysql

This is my input table (player table) with playerID and their played dates, I want this to be checked against milestone table, which has details on when player achieved his milestones
I want to check the milestone table against player table to see if they played within 60 days from the milestone date

  • if they did not play they are a 60 day churner, but if they returned back after 60 days then their status is a returner.
  • If they did not return back at all then they are a 60 day churner
  • If they played within 60 days after the milestone date then they are a active player

player table

player-id date_played
1 1/10/2022
1 1/11/2022
1 1/12/2022
1 1/13/2022
1 5/13/2022
1 5/14/2022
1 5/15/2022
2 1/15/2022
2 2/15/2022
3 8/15/2022
4 1/5/2022
4 5/5/2022

milestone table

player_id milestonedate
1 1/13/2022
2 2/15/2022
3 8/15/2022
4 1/5/2022

My Output

player_id milestone_date churn_status player_status
1 1/13/2022 60 day churner Returner
2 2/15/2022 60 day churner Churner
3 8/15/2022 60 day churner Churner
4 1/5/2022 Active player Returner

How can I achieve this

Tags:

2

Answers


  1. 0

    Use left joins to match plays within 60 days and after 60 days.

    SELECT m.player_id, m.milestone_date, 
    IF(MAX(c.player_id IS NULL), '60 day churner', 'Active player') AS churn_status, 
    IF(MAX(r.player_id IS NULL), 'churner', 'returner') AS player_status
FROM milestone AS m
LEFT JOIN player AS c ON c.player_id = m.player_id AND c.date_played > m.milestone_date AND c.date_played <= DATE_ADD(m.milestone_date, INTERVAL 60 DAY)
LEFT JOIN player AS r ON c.player_id = m.player_id AND c.date_played > DATE_ADD(m.milestone_date, INTERVAL 60 DAY)
GROUP BY m.player_id

    This joins with player twice. The first join gets all the rows where the play is between the mileston date and 60 days later. The second join gets all the rows where the play is after 60 days.

    If there are no matches for either date relationships, that join will return a row where all the columns in player are NULL. The IF() conditions test whether each join contains those null values, and produces the appropriate result in that column.

    Login or Signup to reply.
  2. 0

    It sounds like you are only interested in categorizing based on player rows (weird name for the table, since it seems to represent plays, not players) dated after the milestone date.

    This is fairly straightforward (untested):

    select m.player_id, m.milestone_date,
    case
        when min(p.date_played) <= m.milestone_date + interval 60 day then 'Active player'
        else '60 day churner'
    end churn_status,
    case
        when min(p.date_played) is not null then 'returner'
        else 'churner'
    end player_status
from milestone m
left join player p on p.player_id=m.player_id and p.date_played > m.milestone_date
group by m.player_id

    This finds the minimum date played (if any) for player rows after the milestone date for each player, and uses that to determine the statuses.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search