OSQL exercises

Guide specification
Guide type:	Studio code
Requirements:	None
Recommended reading:	System tutorial, OSQL Testing

Introduction

This is a guide that will help you train your OSQL skills. It contains exercises on various aspects of OSQL and SA Engine. Each exercise contains a problem description and a code block where you can write your solution. There is a short snippet of code in the code block that should help you to get started. At the end of each code block there is a validation function that tests your solution. To check if your solution is correct simply run the code block. Happy solving!

Input files

Some exercises read data from files. The files have to be downloaded before solving those exercises.

http:download_file("https://assets.streamanalyze.com/docs/guides/osql-exercises/input.csv",
    {}, temp_folder() + "input.csv");
http:download_file("https://assets.streamanalyze.com/docs/guides/osql-exercises/input2.csv",
    {}, temp_folder() + "input2.csv");
http:download_file("https://assets.streamanalyze.com/docs/guides/osql-exercises/input3.csv",
    {}, temp_folder() + "input3.csv");

Not connected

To run this code block you must be logged in and your studio instance must be started.

Part 1 - Queries/basic problems

Problem 1-1 - Remove divisible by three

Create a function problem1_1() that takes a vector of integers and returns the vector with all integers divisible by three removed.

Example:

> problem1_1([1,2,3,4,5,6]);
[1,2,4,5]

Solution:

create function problem1_1(vector of integer v) -> vector of integer
  as /* Write your solution here */


validate "problem 1-1"
  check problem1_1([1,2,3,4,5,6]) => [1,2,4,5]
  check problem1_1([10,20,30,40]) => [10,20,40];

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 1-2 - Categorize grade

Create a function problem1_2() that takes a grade (integer) between 0 and 10 and returns a string. The string should say "bad" if the grade is in the interval 0-5, "ok" if the grade is 6-7, "good" if the grade is 8-9, and "great" if the grade is 10.

Example:

> problem1_2(9);
"good"

Solution:

create function problem1_2(integer x) -> Charstring
  as /* Write your solution here */


validate "problem 1-2"
  check problem1_2(4) => "bad"
  check problem1_2(5) => "bad"
  check problem1_2(6) => "ok"
  check problem1_2(7) => "ok"
  check problem1_2(8) => "good"
  check problem1_2(9) => "good"
  check problem1_2(10) => "great";

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 1-3 - Check if outside interval

Create a function problem1_3() that takes an integer and checks if it is outside the interval [5,10]. That is, it should return the integer if it is less than 5 or greater than 10, otherwise it should not return anything.

Example:

> problem1_3(4);
4
> problem1_3(7);
null

Solution:

create function problem1_3(integer i) -> integer
  as /* Write your solution here */


validate "problem 1-3"
  check problem1_3(3) => 3
  check problem1_3(4) => 4
  check problem1_3(5) => null
  check problem1_3(6) => null
  check problem1_3(9) => null
  check problem1_3(10) => null
  check problem1_3(11) => 11
  check problem1_3(12) => 12;

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 1-4 - Argmax

Create a function problem1_4() that takes a vector of integers and finds the index that holds the highest value.

Example:

> problem1_4([19,240,113]);
2
> problem1_4([1,3,7,2,7]);
3
5

Solution:

create function problem1_4(vector of integer v) -> integer
  as /* Write your solution here */


validate "problem 1-4"
  check problem1_4([1,2,3,4,5,6,100,8,9,10]) => 7
  check problem1_4([1,2,3,100,5,6,100,8,9,10]) => bag(4,7);

Not connected

To run this code block you must be logged in and your studio instance must be started.

Part 2 - Stream filters

Problem 2-1

Create a function 'problem2_1()' that takes a a stream of numbers and only emits the numbers that are less than 10.

Example:

> problem2_1(vstream([12,8,7,19,5]));
[1,2,4,5]

Solution:

create function problem2_1(stream of number s) -> stream of number
  as /* Write your solution here */


validate "problem 2-1"
  check extract(problem2_1(vstream([1,2,3,4,5,6,7,8,9,10,11,12,13,14])))
          => bag(1,2,3,4,5,6,7,8,9);

Not connected

To run this code block you must be logged in and your studio instance must be started.

Part 3 - Synthetic/recorded data and csv files

Problem 3-1

Create a function problem3_1() that outputs the following stream of vectors:

[1,2,3]
[4,5,6]
[7,8,9]

Solution:

 create function problem3_1() -> Stream of Vector
   as /* Write your solution here */


validate "problem 3-1"
  check extract(problem3_1()) => bag([1,2,3], [4,5,6], [7,8,9]);

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 3-2

Create a function problem3_2() that returns the following tuples:

(1, "job")
(4, "well")
(7, "done")

Solution:

 create function problem3_2() -> Bag of (Integer, Charstring)
   as /* Write your solution here */


validate "problem 3-2"
  check problem3_2() => bag((1, "job"), (4,"well"), (7,"done"));

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 3-3

Create a function problem3_3() that takes a CSV file as input and outputs a stream of vectors where each vector is one row in the CSV file but with 1 added to each element.

For example, a CSV with the following contents:

1,2,3
4,5,6
7,8,9

would generate a stream of the following vectors:

[2,3,4]
[5,6,7]
[8,9,10]

Solution:

create function problem3_3(Charstring csv_file) -> Stream of Vector
  as /* Write your solution here */


validate "problem 3-3" 
    check extract(problem3_3(temp_folder() + "input.csv"))
            => bag([2,3,4], [5,6,7], [8,9,10]);

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 3-4

Create a function problem3_4() that takes a CSV file as input and parses each row as a Timeval with a utc timestamp and a value. The function should output a stream of timevals where each timeval corresponds to a row in the CSV file.

For example, a CSV with the following contents:

"2023-01-17T09:49:45.449Z",1
"2023-01-17T09:49:45.564Z",2
"2023-01-17T09:49:45.660Z",3

would generate a stream of the following vectors:

ts(|2023-01-17T09:49:45.449Z|, [1])
ts(|2023-01-17T09:49:45.564Z|, [2]),
ts(|2023-01-17T09:49:45.660Z|, [3]),

Solution:

create function problem3_4(Charstring csv_file) -> Stream of Timeval
  as /* Write your solution here */

 
validate "problem 3-4"
    check(extract(problem3_4(temp_folder() + "input2.csv"))) => 
        bag(ts(|2023-01-17T09:49:45.449Z|, [1]),
            ts(|2023-01-17T09:49:45.564Z|, [2]),
            ts(|2023-01-17T09:49:45.660Z|, [3]),
            ts(|2023-01-17T09:49:45.751Z|, [4]),
            ts(|2023-01-17T09:49:45.860Z|, [5]),
            ts(|2023-01-17T09:49:45.954Z|, [6]),
            ts(|2023-01-17T09:49:46.049Z|, [7]),
            ts(|2023-01-17T09:49:46.158Z|, [8]),
            ts(|2023-01-17T09:49:46.250Z|, [9]),
            ts(|2023-01-17T09:49:46.358Z|, [10])
            );

Not connected

To run this code block you must be logged in and your studio instance must be started.

Part 4 - Stream windows

Problem 4-1

Create a function problem4_1() that takes a stream of number and outputs a stream of every second number from the stream.

Example:

> problem4_1(vstream[1,2,3,4,5,6]);
[1,3,5]

Solution:

create function problem4_1(stream of number s) -> stream of number
  as /* Write your solution here */


validate "problem 4-1"
  check extract(problem4_1(vstream([1,2,3,4,5,6]))) => bag(1,3,5)
  check extract(problem4_1(vstream([10,20,99,40]))) => bag(10,99);

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 4-2

Create a function problem4_2() that takes a stream of numbers, computes a moving average over sequences of five with a distance of one, and outputs these as a stream of numbers

Example:

> problem4_2(vstream[1,1,1,1,1,2,2,2,2,2]);
[1,1.2,1.4,1.6,1.8,2]

Solution:

create function problem4_2(stream of number s) -> stream of number
  as /* Write your solution here */


validate "problem 4-2"
  check extract(problem4_2(vstream([1,1,1,1,1,2,2,2,2,2]))) => bag(1,1.2,1.4,1.6,1.8,2);

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 4-3

Create a function problem4_3() that takes a stream of timevals and samples the first 100 milliseconds from every second.

Example: The file input3.csv contains 500 timevals created during 5 seconds. Use ts(diota(0.01,1,500)) to create a similar stream, except with your current time. The resulting stream should look like this (but with different timestamps):

ts(|2023-01-17T14:22:25.349Z|, [[1],[2],[3],[4],[5],[6],[7],[8],[9]])
ts(|2023-01-17T14:22:26.349Z|, [[100],[101],[102],[103],[104],[105],[106],[107],[108],[109]])
ts(|2023-01-17T14:22:27.349Z|, [[201],[202],[203],[204],[205],[206],[207],[208],[209],[210]])
ts(|2023-01-17T14:22:28.349Z|, [[301],[302],[303],[304],[305],[306],[307],[308],[309],[310]])
ts(|2023-01-17T14:22:29.349Z|, [[401],[402],[403],[404],[405],[406],[407],[408],[409],[410]])

Solution:

create function problem4_3(Stream of Timeval tv) -> Stream of Timeval of vector
  as /* Write your solution here */


create function read_test_data(Charstring csv_file) -> Stream of Timeval
    as select Stream of t 
    from Timeval t, Vector v, Number value, charstring timestring
    where v in csv:file_stream(csv_file)
    and [timestring, value] = v
    and t = ts(parse_iso_timestamp(timestring), value);

validate "problem 4-3"
    check(extract(problem4_3(read_test_data(temp_folder() + "input3.csv")))) =>
    bag(ts(|2023-01-17T14:22:25.349Z|, [[1],[2],[3],[4],[5],[6],[7],[8],[9]]),
        ts(|2023-01-17T14:22:26.349Z|, [[100],[101],[102],[103],[104],[105],[106],[107],[108],[109]]),
        ts(|2023-01-17T14:22:27.349Z|, [[201],[202],[203],[204],[205],[206],[207],[208],[209],[210]]),
        ts(|2023-01-17T14:22:28.349Z|, [[301],[302],[303],[304],[305],[306],[307],[308],[309],[310]]),
        ts(|2023-01-17T14:22:29.349Z|, [[401],[402],[403],[404],[405],[406],[407],[408],[409],[410]]));

Not connected

To run this code block you must be logged in and your studio instance must be started.

Problem 4-4

Create a function problem4_4() that takes a stream of timevals and, whenever there is a value divisible with 100, gets a timewindow of 100 ms.

Example: from the diota(0.1,1,500) stream in the previous exercise there should be five windows, as in the bag seen below.

ts(|2023-01-17T14:22:26.255Z|, [92,93,94,95,96,97,98,99,100])
ts(|2023-01-17T14:22:27.245Z|, [190,191,192,193,194,195,196,197,198,199,200])
ts(|2023-01-17T14:22:28.238Z|, [289,290,291,292,293,294,295,296,297,298,299,300])
ts(|2023-01-17T14:22:29.239Z|, [390,391,392,393,394,395,396,397,398,399,400])
ts(|2023-01-17T14:22:30.247Z|, [492,493,494,495,496,497,498,499,500]

Solution:

create function hundred_p(Timeval of Integer tn) -> Boolean
    as select mod(value(tn),100) = 0;
 
create function problem4_4(Stream of Timeval tv) -> Stream of Timeval of vector
  as /* Write your solution here */
 
 
validate "problem 4-4"
    check(extract(problem4_4(read_test_data(temp_folder() + "input3.csv")))) =>
    bag(ts(|2023-01-17T14:22:26.255Z|, [92,93,94,95,96,97,98,99,100]),
        ts(|2023-01-17T14:22:27.245Z|, [190,191,192,193,194,195,196,197,198,199,200]),
        ts(|2023-01-17T14:22:28.238Z|, [289,290,291,292,293,294,295,296,297,298,299,300]),
        ts(|2023-01-17T14:22:29.239Z|, [390,391,392,393,394,395,396,397,398,399,400]),
        ts(|2023-01-17T14:22:30.247Z|, [492,493,494,495,496,497,498,499,500])
        );

Not connected

To run this code block you must be logged in and your studio instance must be started.