how to get the count of hattricks based on the Streak column for the name.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

MatchNo	Match_date      Name    Streak
1       2015-04-08      Aditya	1
2       2015-04-09      Aditya	0
3       2015-04-10      Aditya	0
4       2015-04-11      Aditya	0
5       2015-04-11      Aditya	0
6       2015-04-12      Aditya	0
7       2015-04-12      Aditya	1
8       2015-04-13      Aditya	1
9       2015-04-14      Aditya	0
10      2015-04-15      Aditya	1
11      2015-04-16      Aditya	0
12      2015-04-17      Aditya	0
13      2015-04-18      Aditya	0
14      2015-04-18      Aditya	0
15      2015-04-19      Aditya	1
16      2015-04-19      Aditya	1
17      2015-04-20      Aditya	1
18      2015-04-21      Aditya	1
19      2015-04-22      Aditya	1
20      2015-04-22      Aditya	0
21      2015-04-23      Aditya	0

[edit]Code block added - OriginalGriff[/edit]

Posted 23-Apr-15 20:42pm

Prasad Dds

Updated 23-Apr-15 21:18pm

OriginalGriff

v3

Add a Solution

Comments

OriginalGriff 24-Apr-15 3:19am

Which one(s) of those are hattricks?
What rule decides?

pratap420 24-Apr-15 12:07pm

what's your expected output?

Prasad Dds 24-Apr-15 12:48pm

here streak means result, if streak 1 means won and 0 means lost. so if a person won 3 in a row then count is 1, if won 4 in a row then count is 1, if won 5 in a row count 1, if won 6 in a row then 2, 7 in a row 2, 8 in row 2 , 9 in a row 3. if he won 5 in a row and 6th one lost then its count is 1. hope it gives some idea

Prasad Dds 24-Apr-15 12:50pm

My expected output for above data is :

name no_hattricks
aditya 1

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-04-25T03:54:00

This is the fairly common exercise of finding streaks (runs, patterns) in your data.

There is an excellent article[^] by Jeff Smith at SqlTeam.com that points out the "trick" you need exploit - each "run" of wins is preceded by a (single) loss. You can use this to identify a new winning streak by comparing its streak value against the value from the previous row. Give each block of wins and losses a number - RunGroup in the following sql

SQL

SELECT Match_Date, MatchNo, Player,
  (SELECT COUNT(*)
   FROM results G
   WHERE G.streak <> GR.streak
   AND G.MatchNo <= GR.MatchNo) as RunGroup
FROM results GR
WHERE streak = 1

Which will give the following result

Match_date     MatchNo  Player       RunGroup
2015-04-08	1	Aditya		0
2015-04-12	7	Aditya		5
2015-04-13	8	Aditya		5
2015-04-15	10	Aditya		6
2015-04-19	15	Aditya		10
2015-04-19	16	Aditya		10
2015-04-20	17	Aditya		10
2015-04-21	18	Aditya		10
2015-04-22	19	Aditya		10

If we turn that sql into a Common Table Expression we can then use GROUP BY to get a count of the number of matches in each RunGroup:

SQL

;WITH CTE AS (
SELECT Match_Date, MatchNo, Player,
  (SELECT COUNT(*) 
   FROM results G 
   WHERE G.streak <> GR.streak 
   AND G.MatchNo <= GR.MatchNo) as RunGroup 
FROM results GR
WHERE streak = 1 
)
SELECT Player, 
  COUNT(*)
FROM CTE
GROUP BY Player, RunGroup

which gives the results

Aditya  1
Aditya  2
Aditya  1
Aditya  5

Because you are only interested in hattricks (sequences of 3 wins in a row) then you need to restrict the results using

SQL

HAVING COUNT(*) >= 3

so that you only get the row

Aditya  5

To get your expected result you also need to change the final COUNT(*) to get the (integer) value divided by 3 i.e.

COUNT(*) / 3 as Games