— This week will be FIFA World Cup week. All challenges will be related to FIFA World Cup only for this week. — Find the average interval of winning for a team if a team has won World cup more than once. Hence, if a team won in 1950, 1964 and 1970 1964-1950 = 14 1970-1964 = 6 Average = (14+6)/2 = 10 (It is divided by 2 as there are 2 intervals only) Sort on Avg Winning Interval.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 74
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Avg Interval Between Wins with Power Query
Power Query solution 1 for Avg Interval Between Wins, proposed by Brian Julius:
let
Source = AvgIntervalRaw,
InitSort = Table.Sort(Source, {{"Winners", Order.Ascending}, {"Year", Order.Ascending}}),
TableA = Table.AddIndexColumn(InitSort, "Index", 0, 1, Int64.Type),
TableB = Table.PrefixColumns(TableA, "Prev"),
IdxAdd1 = Table.TransformColumns(TableB, {{"Prev.Index", each _ + 1, type number}}),
JoinTable = Table.Join(
TableA,
{"Index", "Winners"},
IdxAdd1,
{"Prev.Index", "Prev.Winners"},
JoinKind.Inner
),
CalcInterval = Table.AddColumn(
JoinTable,
"Interval",
each Number.FromText([Year]) - Number.FromText([Prev.Year])
),
GroupAvg = Table.Group(
CalcInterval,
{"Winners"},
{{"Avg Winning Interval", each List.Average([Interval]), type number}}
),
Sort = Table.Sort(
GroupAvg,
{{"Avg Winning Interval", Order.Ascending}, {"Winners", Order.Ascending}}
)
in
SortPower Query solution 2 for Avg Interval Between Wins, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
fnAvgInterval = (Years) =>
let
Avg = List.Average(
List.Transform(
List.Zip({List.Skip(Years, 1), List.RemoveLastN(Years, 1)}),
each _{1} - _{0}
)
)
in
Avg,
Grouped = Table.Group(Source, {"Winners"}, {{"Avg Winning Interval", each fnAvgInterval([Year])}}),
Filtered = Table.SelectRows(
Grouped,
each [Avg Winning Interval] <> null and [Avg Winning Interval] <> ""
),
Sorted = Table.Sort(
Filtered,
{{"Avg Winning Interval", Order.Ascending}, {"Winners", Order.Ascending}}
)
in
SortedPower Query solution 3 for Avg Interval Between Wins, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "WinningInterval"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Winners"},
{
{
"Avg Winning Interval",
each (List.Max([Year]) - List.Min([Year])) / (List.Count([Year]) - 1)
},
{"Wins", each Table.RowCount(_), Int64.Type} /*optional!*/
}
),
#"Filtered Rows" = Table.SelectRows(
#"Grouped Rows",
each (not Number.IsNaN([Avg Winning Interval]))
),
#"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Avg Winning Interval", Order.Ascending}})
in
#"Sorted Rows"Power Query solution 4 for Avg Interval Between Wins, proposed by Antriksh Sharma:
let
Source = Data,
GroupedRows = Table.Group(
Source,
{"Winners"},
{
{
"Avg Winning Interval",
(Group) =>
let
S = Table.AddColumn(
Group,
"Adjustment",
(OuterYear) =>
OuterYear[Year]
- Table.FirstN(Table.SelectRows(Group, each [Year] < OuterYear[Year]), 1)[Year]{
0
}?
)[Adjustment],
C = List.NonNullCount(S),
Result = List.Sum(S) / C
in
Result,
Int64.Type
}
}
),
FilteredRows = Table.SelectRows(GroupedRows, each ([Avg Winning Interval] <> null)),
SortedRows = Table.Sort(FilteredRows, {{"Avg Winning Interval", Order.Ascending}})
in
SortedRowsPower Query solution 5 for Avg Interval Between Wins, proposed by Owen Price:
Here's another PQ solution:
https://gist.github.com/ncalm/75ef387116db82c61816b5968f466feb
Table.PositionOf makes it easy to replicate SQL's LEAD or LAG within a Table.Group function.
Power Query solution 6 for Avg Interval Between Wins, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
MultipleWinsOnly = Table.SelectRows(Source, each (try Source{[Winners = [Winners]]})[HasError]),
Result = Table.Group(
MultipleWinsOnly,
{"Winners"},
{
{
"Avg Winning Interval",
each
let
y = List.Sort([Year], 1)
in
List.Average(
List.RemoveNulls(
List.Transform(List.Positions(y), each try y{_} - y{_ + 1} otherwise null)
)
)
}
}
),
Sort = Table.Sort(Result, {{"Avg Winning Interval", 0}})
in
SortSolving the challenge of Avg Interval Between Wins with Excel
Excel solution 1 for Avg Interval Between Wins, proposed by John V.:
=LET(
w,
B2:B22,
u,
UNIQUE(
FILTER(
w,
COUNTIF(
w,
w
)>1
)
),
SORT(
HSTACK(
u,
MAP(
u,
LAMBDA(
x,
LET(
f,
FILTER(
A2:A22,
w=x
),
AVERAGE(
DROP(
f,
-1
)-DROP(
f,
1
)
)
)
)
)
),
2
)
)
Shortening another approach here:
=LET(y,
A2:A22,
w,
B2:B22,
u,
UNIQUE(
w
),
b,
(MAXIFS(
y,
w,
u
)-MINIFS(
y,
w,
u
))/(COUNTIF(
w,
u
)-1),
SORT(
FILTER(
HSTACK(
u,
b
),
1-ISERR(
b
)
),
2
))Excel solution 2 for Avg Interval Between Wins, proposed by محمد حلمي:
=LET(
b,B2:B22,
c,UNIQUE(b),
o,MAP(c,LAMBDA(e,
REDUCE(0,e,LAMBDA(a,d,
LET(v,FILTER(A2:A22,d=b),
AVERAGE(IFERROR(DROP(v,-1)-DROP(v,1),))))))),
v,SORT(HSTACK(c,o),2),
FILTER(v,DROP(v,,1)>0))Excel solution 3 for Avg Interval Between Wins, proposed by 🇰🇷 Taeyong Shin:
=LET(
y, A2:A22,
w, B2:B22,
cnt, SUM(SIGN(UNIQUE(COUNTIF(w, w))-1)),
nums, XMATCH(w, w),
Loop, LAMBDA(ME,arr,n,
LET(
k, MODE.MULT(arr),
c, XLOOKUP(k, nums, w),
f, FILTER(arr, ISNA(XMATCH(arr, k))),
IF(n = cnt, c, VSTACK(c, ME(ME, f, n + 1)))
)
),
Win, Loop(Loop, nums, 1),
Do, LAMBDA(ME,arr,n,
LET(
ys, FILTER(y, w = INDEX(arr, n)),
avg, AVERAGE(DROP(ys, -1) - DROP(ys, 1)),
IF(n = ROWS(Win), avg, VSTACK(avg, ME(ME, Win, n + 1)) )
)
),
SORT(HSTACK(Win, Do(Do, Win, 1)), 2)
)Excel solution 4 for Avg Interval Between Wins, proposed by 🇰🇷 Taeyong Shin:
=LET(
w,
B2:B22,
GROUPBY(
w,
A2:A22,
LAMBDA(
x,
AVERAGE(
DROP(
x,
-1
)-DROP(
x,
1
)
)
),
,
0,
2,
COUNTIF(
w,
w
)>1
)
)Excel solution 5 for Avg Interval Between Wins, proposed by Kris Jaganah:
=LET(a,A2:A22,b,B2:B22,c,SORT(HSTACK(b,a)),d,HSTACK(c,BYROW(c,LAMBDA(x,SUM(IF(x=b,1,0))))),e,IFNA(IF(CHOOSECOLS(d,1)<>DROP(CHOOSECOLS(d,1),1),0,CHOOSECOLS(d,2)-DROP(CHOOSECOLS(d,2),1)),0),g,HSTACK(d,e),i,HSTACK(UNIQUE(CHOOSECOLS(g,1)),BYROW(UNIQUE(CHOOSECOLS(g,1)),LAMBDA(y,SUM(IF(CHOOSECOLS(g,1)=y,CHOOSECOLS(g,4),0))))),j,UNIQUE(HSTACK(CHOOSECOLS(d,1),CHOOSECOLS(d,3)-1)),k,HSTACK(CHOOSECOLS(j,1),IFERROR(CHOOSECOLS(i,2)/CHOOSECOLS(j,2),0)),l,FILTER(k,CHOOSECOLS(k,2)<>0),SORTBY(l,CHOOSECOLS(l,2),1))Excel solution 6 for Avg Interval Between Wins, proposed by Julian Poeltl:
=LET(Y,
A2:A22,
W,
B2:B22,
U,
UNIQUE(
W
),
R,
MAP(U,
LAMBDA(A,
LET(F,
FILTER(
Y,
W=A
),
SUM(
IFNA(
F-DROP(
F,
1
),
0
)
)/(ROWS(
F
)-1)))),
SORT(
FILTER(
HSTACK(
U,
R
),
ISNUMBER(
R
)
),
2,
1
))Excel solution 7 for Avg Interval Between Wins, proposed by Timothée BLIOT:
=LET(Y,
A2:A22,
W,
B2:B22,
V,
BYROW(W,
LAMBDA(a,
SUMPRODUCT(1*(W=a)))),
U,
UNIQUE(
FILTER(
W,
V>1
)
),
K,
IFERROR(
TEXTSPLIT(
TEXTJOIN(
"/",
1,
BYROW(
U,
LAMBDA(
a,
TEXTJOIN(
",",
1,
FILTER(
Y,
W=a
)
)
)
)
),
",",
"/"
),
""
),
I,
IFERROR(MAKEARRAY(ROWS(
K
),
COLUMNS(
K
),
LAMBDA(a,
b,
IF((bExcel solution 8 for Avg Interval Between Wins, proposed by Hussein SATOUR:
=LET(y,
A2:A22,
w,
B2:B22,
a,
UNIQUE(
w
),
b,
(MAXIFS(
y,
w,
a
) - MINIFS(
y,
w,
a
)) / (COUNTIF(
w,
a
) - 1),
SORT(
FILTER(
HSTACK(
a,
b
),
NOT(
ISERR(
b
)
)
),
2
))Excel solution 9 for Avg Interval Between Wins, proposed by Duy Tùng:
=GROUPBY(B2:B22,
A2:A22,
LAMBDA(x,
(@x-MIN(
x
))/(ROWS(
x
)-1)),
,
0,
2,
COUNTIF(
B2:B22,
B2:B22
)>1)Excel solution 10 for Avg Interval Between Wins, proposed by Bhavya Gupta:
=LET(
Y,
A2:A22,
W,
B2:B22,
u,
UNIQUE(
W
),
r,
BYROW(
u,
LAMBDA(
x,
LET(
f,
SORT(
FILTER(
Y,
W=x
)
),
AVERAGE(
DROP(
f,
1
)-DROP(
f,
-1
)
)
)
)
),
SORT(
FILTER(
HSTACK(
u,
r
),
ISNUMBER(
r
)
),
{2,
1}
)
)Solving the challenge of Avg Interval Between Wins with Python
Python solution 1 for Avg Interval Between Wins, proposed by Owen Price:
df_clean = df_rawdata.copy()
dfgb_winners = df_clean.groupby('Winners')
df_clean['NextYear'] = dfgb_winners['Year'].shift(1)
df_clean = df_clean.dropna().copy()
df_clean['Gap'] = df_clean.NextYear - df_clean.Year
result = df_clean
.groupby('Winners')['Gap']
.agg('mean')
.reset_index()
.sort_values(by = ['Gap','Winners'])
result
Solving the challenge of Avg Interval Between Wins with DAX
DAX solution 1 for Avg Interval Between Wins, proposed by Zoran Milokanović:
EVALUATE
GROUPBY(
FILTER(
ADDCOLUMNS(Input, "I", VAR P = SELECTCOLUMNS(OFFSET(-1, ORDERBY(Input[Year]), PARTITIONBY(Input[Winners])), Input[Year]) RETURN IF(ISBLANK(P), P, Input[Year] - P)
),
NOT(ISBLANK([I]))
),
Input[Winners], "Average", AVERAGEX(CURRENTGROUP(), [I])
)
ORDER BY
[Average], Input[Winners]
Solving the challenge of Avg Interval Between Wins with SQL
SQL solution 1 for Avg Interval Between Wins, proposed by Zoran Milokanović:
SELECT
F.WINNERS
,AVG(F.YEAR - F.PREVIOUS_APPEARANCE) AS AVG_WINNING_INTERVAL
FROM
(
SELECT
D.YEAR
, D.WINNERS
,LAG(D.YEAR) OVER (PARTITION BY D.WINNERS ORDER BY D.YEAR) AS PREVIOUS_APPEARANCE
FROM DATA D
) F
WHERE
F.PREVIOUS_APPEARANCE IS NOT NULL
GROUP BY
F.WINNERS
ORDER BY
2, 1
;