CODEWITHSUNDEEP
sql-serverdategroup-byaggregate-functions
JammingThebBits
JammingThebBits

Reputation: 752

How to find ratio value between two consecutive dates

Given a table which consists of: ID_User, Date

I'd like to find the ratio between every two consecutive days, The ratio between the same people who attended day x and day x+1. i'll give an example:

let's say :

Bill 12155 2018-05-01
Jim 52135 2018-05-01
Homer 52135 2018-05-01
Jecki 56135 2018-05-01
Michael 45644 2018-05-02
Jim 52135 2018-05-02
Jessy 45645 2018-05-02
Homer 52135 2018-05-02

So the ratio would be 2/4 = 0.5

I tried resolving it on my own for the last day but had some struggles. I started by grouping by date:

Select Date, ID_USER
GROUP BY DATE, ID_USER
ORDER BY DATE, ID_USER

can someone please give me some pointers,

Thank you all!

Upvotes: 1

Views: 257

Answers (2)

shawnt00
shawnt00

Reputation: 17915

The self-join solution is valid. You might try this approach as well:

with data as (
    select "date",
        case when dateadd(day, 1, "date") = 
            lead("date") over (partition by id order by "date")
                then 1 end as returned
    from T
)
select "date", count(returned) * 1. / count(*) as ratio
from data
group by "date";

If you want to eliminate the final date since it's always zero, you could easily add case when "date" <> max("date") over () then 1 end as notfinal and filter based on that.

https://rextester.com/HHL82126

Upvotes: 1

dvo
dvo

Reputation: 2153

Try this: 

SELECT t1.[Date], 
      ( CONVERT(decimal, SUM(CASE WHEN t2.[ID] IS NOT NULL THEN 1 ELSE 0 END) ) / COUNT(t1.[ID]) ) AS [Ratio]
FROM @YourTbl t1
LEFT OUTER JOIN @YourTbl t2 ON t2.[ID] = t1.[ID] AND t2.[Date] = DATEADD(DAY, 1, t1.[Date]) 
GROUP BY t1.[Date]

Group your data by the first Date (in your sample, 05-01-2018).

Then, self-join the table by doing a LEFT OUTER JOIN so you have the full list of data and a second list of only the data where the same user (based on ID) is in the data again for the next day (DATEADD( DAY, 1, ... )).

Then you can tell if any user has attended two days in a row based on a given date by checking any field in t2 to be NULL.

To get a ratio of Users who attended t1.[Date] and the next date t2.[Date], total up the users in t2 where the ID is NOT NULL and divide it by the total count of users for that day in t1. Now, since SUM returns an INT in this case and you need a decimal, CONVERT the SUM to DECIMAL and you will get a decimal number.

Here are the results for your sample data: Note: After changing the ID of either Jim or Homer since they originally had the same ID. 

Date          Ratio
2018-05-01    0.50000000000
2018-05-02    0.00000000000

Upvotes: 1

Related Questions