Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEANice and straightforward!
Here's my solution.
A few comments:
In this, I think QXuery could offer a more elegant solution. This script uses Window function and is quite compact:
xquery version "3.0";
let $docSorted := for $i in doc("AlteryxChallenge_323.xml")/dataset/record
order by $i/BookName, xs:integer(tokenize($i/Date,"/")[1]), xs:integer(tokenize($i/Date,"/")[2])
return $i
return
<table>
{
for tumbling window $w in $docSorted
start $s previous $s-prev when $s/Availability = "0" and ($s/BookName != $s-prev/BookName or $s-prev/Availability != "0")
end $e next $e-next when $e-next/Availability != "0" or $e/BookName != $e-next/BookName
return
<window>
<BookName>{data($s/BookName)}</BookName>
<StartDate>{data($s/Date)}</StartDate>
<EndDate>{data($e/Date)}</EndDate>
<NoOfDays>{count($w/BookName)}</NoOfDays>
<ReasonsByDay>{string-join($w ! Reason,",")}</ReasonsByDay>
</window>
}</table>
Here is the resulting xml:
<table>
<window>
<BookName>Godzilla</BookName>
<StartDate>2/3/2022</StartDate>
<EndDate>2/5/2022</EndDate>
<NoOfDays>3</NoOfDays>
<ReasonsByDay>Soldout,Soldout,Soldout</ReasonsByDay>
</window>
<window>
<BookName>Godzilla</BookName>
<StartDate>2/12/2022</StartDate>
<EndDate>2/15/2022</EndDate>
<NoOfDays>4</NoOfDays>
<ReasonsByDay>No Stock,No Stock,No Stock,Soldout</ReasonsByDay>
</window>
<window>
<BookName>Growth</BookName>
<StartDate>2/1/2022</StartDate>
<EndDate>2/2/2022</EndDate>
<NoOfDays>2</NoOfDays>
<ReasonsByDay>Soldout,Soldout</ReasonsByDay>
</window>
<window>
<BookName>Growth</BookName>
<StartDate>2/5/2022</StartDate>
<EndDate>2/6/2022</EndDate>
<NoOfDays>2</NoOfDays>
<ReasonsByDay>No Stock,No Stock</ReasonsByDay>
</window>
<window>
<BookName>Growth</BookName>
<StartDate>2/12/2022</StartDate>
<EndDate>2/15/2022</EndDate>
<NoOfDays>4</NoOfDays>
<ReasonsByDay>Soldout,Soldout,Soldout,Soldout</ReasonsByDay>
</window>
<window>
<BookName>Roundup</BookName>
<StartDate>2/2/2022</StartDate>
<EndDate>2/5/2022</EndDate>
<NoOfDays>4</NoOfDays>
<ReasonsByDay>Damage,Damage,Damage,Damage</ReasonsByDay>
</window>
<window>
<BookName>Roundup</BookName>
<StartDate>2/14/2022</StartDate>
<EndDate>2/15/2022</EndDate>
<NoOfDays>2</NoOfDays>
<ReasonsByDay>No Stock,No Stock</ReasonsByDay>
</window>
<window>
<BookName>State of Union</BookName>
<StartDate>2/5/2022</StartDate>
<EndDate>2/12/2022</EndDate>
<NoOfDays>8</NoOfDays>
<ReasonsByDay>Soldout,Soldout,Soldout,Soldout,No Stock,Soldout,No Stock,No Stock</ReasonsByDay>
</window>
</table>