1.Find all prime numbers within 20.

MySQL8:
with recursive t(n) as (
select 1
union all select n+1 from t where n<20)
select n from t
where n!=1 and n not in (select t1.nt2.n from t t1 join t t2 on t1.n<=t2.n where t1.n>1 and t2.n between 2 and 20/2 and t1.nt2.n<=20);

SPL script:

	A
1	=to(20)
2	=to(2,20/2)
3	=A2.(A1.step(,*2))
4	=A1.to(2,)\A3.conj()

A3: Calculate the n times (n>1) of each of A2’s members within 20 and return results as a sequence.
A4: Within 20, except for 1 and all composite numbers, others are the prime numbers. A3.conj()gets the composite numbers within 20.

2. Find the longest consecutively rising days for the stock 000651.

MySQL8:
with recursive t1 as (select *,row_number() over(order by tdate) rn from stktrade where sid=‘000651’),
t2 as (select ,0 rise from t1 where rn=1
union all
select t1., if(t1.close>t2.close,t2.rise+1,0) from t1 join t2 on t1.rn=t2.rn+1)
select max(rise) from t2;

SPL script:

	A
1	=connect(“mysql”)
2	=A1.query@x(“select   * from stktrade where sid=’000651’order by tdate”)
3	=a=0,A2.max(a=if(close>close[-1],a+1,0))

A2: Sort records by transaction dates in ascending order.
A3: Loop over each record to calculate the value of variable a. Plus 1 if the current closing price is higher than the previous one; otherwise reset the value as 0. Then find the maximum value.

3. Calculate stock 300469’s percentage decrease in closing price from historical high to the price in Feb. 14, 2018.

MySQL8:
select 1-t2.close/t1.close fall
from (select max(close) close from stktrade where sid=‘300469’) t1,
(select close from stktrade where sid=‘300469’ and tdate=‘2018-02-14’) t2;

SPL script:

	A
1	=connect(“mysql”)
2	=A1.query@x(“select   * from stktrade where sid=’300469’order by tdate”)
3	=A2.top@1(1,(if(tdate==date(“2018-02-14”),A4=close),-close))
4	=1+@/A3

A3: Loop over A2’s records to calculate the minimum value of – close (the maximum value of close) while assigning the closing price close in February 14, 2018 to A4.
A5: @ represents the current cell value.

4. Calculate stock 300469’s daily cumulative transaction volume from Jan. 1 to 10, 2018

MySQL8:
select tdate,volume,sum(volume) over(order by tdate) cum
from stktrade
where sid=‘300469’ and tdate between ‘2018-01-01’ and ‘2018-01-10’;

SPL script:

	A
1	=connect(“mysql”)
2	=A1.query@x(“select   tdate,volume,0 cum from stktrade where sid=’300469’and tdate between  ’2018-01-01’and’2018-01-10’order by tdate”)
3	=A2.run(cum=cum[-1]+volume)

A3: Loop over A2’s each record to re-assign the sum of previous cum value and the current volume value to the volume field value and return a new A2.

5. Find the number of days stock 300469 uses to reach a transaction volume of 1000000 shares

MySQL8:
with t as (select row_number() over(order by tdate) rn,
sum(volume) over(order by tdate) cum
from stktrade
where sid=‘300469’ and tdate>=‘2018-01-01’)
select min(rn) from t where cum>=1000000;

SPL script:

	A
1	=connect(“mysql”)
2	=A1.query@x(“select   tdate,volume from stktrade where sid=’300469’and tdate>=‘2018-01-01’  order by tdate”)
3	=A2.iterate((@+=volume,~~+1),0,@>1000000)

A3: A.iterate(x,a,c) assigns a to variable ~~ as the initial value, loops over each record in A2 to calculate expression x and assigns the result to the variable, and then check whether the result of expression c is true. If c’s result is true, continue the loop; otherwise return the current variable value. @ represents the current cell value, which is an intermediate variable here.

6. Count the days in multiple time periods (Count once for overlapping periods)

MySQL8:
with tt(start,end) as (select date’2010-01-07’,date’2010-01-9’
union all select date’2010-01-15’,date’2010-01-16’
union all select date’2010-01-07’,date’2010-01-12’
union all select date’2010-01-08’,date’2010-01-11’),
t as (select * from tt order by start),
tmp as (
select t.start, t.end, @m:=if(@m>@p,@m,@p) m,@p:=end,
case when @m>end then 0 when @m<start then datediff(end,start)+1 else datediff(end,@m) end days
from t,(select @m:=date’1970-01-01’,@p:=@m) s
)
select sum(days) from tmp;

Note: @m is the earliest end date before the current record. @p is the end date of the period the current records falls in. days is the number of days from the previous earliest date to date. See Overcome SQL Headaches: For Set Operations and Getting Row Numbers and Overcome SQL Headaches – For Record Reference to learn more solutions to this question.

SPL script:

	A
1	=connect(“mysql”)
2	=A1.query@x(“select   date’2010-01-07’start,date’2010-01-9’end union all select   date’2010-01-15’,date’2010-01-16’union all select   date’2010-01-07’,date’2010-01-12’union all select   date’2010-01-08’,date’2010-01-11’”)
3	=A2.sort(start)
4	=A3.iterate((@=max(@,   end[-1]), ~~ + if(@>=end:0, @<start:end-start+1, end-@)), 0)

A4: A.iterate() function loops over all members of A3’s table sequence as its third parameter is absent.

7. Get transaction information and cumulative turnover rate from Jun. 11-15, 2018 for stock 300469 and stock 600036

MySQL8:
with k as (select sid,circulation,tdate start,lead(tdate,1, date_add(now(),interval 1 day))over(partition by sid order by tdate) end
from stocks)
select t.*, k.circulation circ, sum(t.volume/k.circulation/10000) over(partition by sid order by tdate) rate
from stktrade t join k on t.sid=k.sid and t.tdate>=k.start and t.tdate<k.end
where t.sid in (‘300469’,‘600036’) and t.tdate between ‘2018-06-11’ and ‘2018-06-15’;

SPL script:

	A
1	=connect(“mysql”)
2	=A1.query(“select   * from stocks where sid in (‘600036’,‘300469’) order by sid,tdate”)
3	=A1.query@x(“select   * from stktrade where sid in (‘600036’,‘300469’) and tdate between  ’2018-06-11’and’2018-06-15’order by sid,tdate”)
4	=A3.derive((p=A2.pselect@bs(sid:A3.sid,tdate:A3.tdate),   p=if(p<0,-p-1,p), A2(p).circulation):circ, iterate(~~+volume/circ/10000,0;sid):rate)

A2: Sort capital stocks in circulation by stock IDs and transaction dates.
A3: Sort transaction information by stock IDs and transaction dates.
A4: A2.pselect@bs(sid:A3.sid,tdate:A3.tdate) gets the capital stocks in circulation in the specified time period and returns their row numbers if they are found out; otherwise returns their positions for insertion. p=if(p<0,-p-1,p) gets the row numbers of capital stocks in circulation on the current transaction date from A2. Then get the capital stocks to assign them to circ column. The loop function iterate(x,a;G) assigns a’s value to variable ~~ at the start of each round of loop and or when G’s value changes and then assigns expression x’s result to the variable.

8. Calculate stock 600036’s average closing prices in 20 days for each date from Jan. 1-10, 2018

MySQL8:
with t as (select *,row_number() over(order by tdate) rn from stktrade where sid=‘600036’),
t1 as (select * from t where tdate between ‘2018-01-01’ and ‘2018-01-10’)
select t1.tdate, t1.close, avg(t.close) ma20
from t1 join t on t.rn between t1.rn-19 and t1.rn
group by t1.tdate;

SPL script:

	A
1	=connect(“mysql”)
2	=A1.query@x(“select   tdate,close from stktrade where sid=’600036’order by tdate”)
3	=A2.pselect@a(tdate>=date(“2018-01-01”)   && tdate<=date(“2018-01-10”))
4	=A2(A3).derive(A2.calc(A3(#),avg(close[-19:0])):ma20)

A3: Get row numbers of all eligible records from A2.
A4: A2(A3) gets records from A2 by their row numbers. Then the statement generates new records by copying the original records’ field names and values and by adding a new field ma20 whose values are average closing prices in 20 days for A2’s records.

9. Get names, population, heads of state and official languages count for countries with the most official languages

MySQL8:
select Name, Population, HeadOfState, top.Num
from world.country
join (
select countrycode, count() as num
from world.countrylanguage
where isofficial=‘T’
group by countrycode
having num = (
select max(summary.n)
from (
select countrycode, count() as n
from world.countrylanguage
where isofficial=‘T’
group by countrycode
) as summary
)
) as top on country.code=top.countrycode;

SPL script:

	A
1	=connect(“mysql”)
2	=A1.query(“select   * from world.country”)
3	=A1.query@x(“select   * from world.countrylanguage where isofficial=‘T’”)
4	=A3.group(CountryCode).maxp@a(~.len())
5	=A4.news(A2.select(Code:CountryCode);Name,Population,HeadOfState,A4.~.len():Num)

A4: Group languages by CountryCode and get groups with the most members.
A5: A4.news loop over each of A4’s group to get corresponding countries from A2 and then create new records where Num field values are the number of members in the current group.