Creating Queries

Question 1

What does the ‘GROUP BY’ function do?

Answer 1

*If your attribute has more than one category you can use the group by function to help see it in a stacked (eg. stacked bar chart) format

Question 2

What 4 SiGNAL operators are available?

Answer 2

1. arithmetic
2. comparison
3. logical
4. mapping

Question 3

Explain Arithmetic

Answer 3

+ - * / %(modulo i.e remainder)

eg. SELECT discount / price * 100
FROM THIS_PROCESS

Question 4

Explain Comparison

Answer 4

> < >= <= = <>(not equal to)

Question 5

Explain Logical

Answer 5

AND , IN, NOT, OR

Question 6

Explain Mapping

Answer 6

IF (returning value based on condition)

eg. SELECT IF (condition, then, else)

Question 7

Explain ‘Order by’ function

Answer 7

Function to sort the result set according to sorting criteria

Question 8

Example of the order by function

Answer 8

‘ORDER BY column number ASC/DESC

Question 9

Explain ‘Limit’ function

Answer 9

Function to limit the number of records in the result set

Question 10

Example of the ‘Limit’ function

Answer 10

LIMIT no. of entries you want

Question 11

What is Alias with and example

Answer 11

Aliases help to give data/column headers a temporary name to make them easier to read.

SELECT expression AS [“ALIAS”]
e.g SELECT “Case ID” as “Customer ID” (on the table produced it will be labelled as customer ID)

Question 12

What is a subquery

Answer 12

a query that is nested inside a SELECT statement, or inside another sub query

Question 13

when is subqueries used?

Answer 13

when retrieving data on an event-level

Question 14

What are 3 date functions

Answer 14

DATE_TRUNC
NOW()
DURATION

Question 15

Explain DATE_TRUNC

Answer 15

limits time stamps to certain units of time by removing detail

eg. SELECT….
DATE_TRUNC (‘Month’, (SELECT(end_time))).
as ‘Month in 2017’

[created row called ‘Month in 2017’, where it produced dates that stayed as the first day on every month in 2017, with the month only changing]

Question 16

Explain NOW()

Answer 16

Helps calculate the duration between the current and given time

e.g SELECT…
(SELECT NOW() - LAST(end_time)) as “Last order”

[created row called ‘last order’, produced durations eg. 3y 4w]

Question 16

Explain DURATION

Answer 16

Compares a duration of time with a threshold, and support strings (e.g “2 weeks”, “3days”)

eg. …
WHERE (SELECT LAST(end_time) - FIRST (End_time)) > DURATION “2weeks”

Question 17

What is the FILTER function for?

Answer 17

allows you to filter an input for an aggregations function

Question 18

Structure of the filter function

Answer 18

SELECT expressions
FILTER (WHERE conditions)
FROM table/process

Question 19

What are matching operators?

Answer 19

Matches allow you to filter cases based on the indicated ‘MATCHES’ terms

Question 20

[explain these matching operators]
A->B
A~>B
^A

Answer 20

[when it says directly followed you have to remember this is mapping a diagram so its the next task]

A->B : A direction followed by B
A~>B : A indirectly or directly followed by B
^A : sequence starts with A in any specific case

Question 21

[explain these matching operators]
A NULL
NOT A
A NOT B
A NOT (B|C)
(A|B)

Answer 21

A NULL : A value is NULL
NOT A : All values that are not A
A NOT B : A directly followed by a value that is not B
A NOT (B|C) : A followed by a value that is not B or C
(A|B) : A or B

Question 22

[explain these matching operators]
B$
A ANY

Answer 22

B$ : Sequence ends with B in any specific case
A ANY : A occurs anywhere in the case

Question 23

What is BEHAVIOUR MATCHES operator with an example

Answer 23

Allows to filter MATCHES even further with expressions not related to task sequence

eg. (trying to obtain values where the end time is greater than a specific date for shipping goods)
WHERE BEHAVIOUR
(event_name = “Ship Goods Standard” and END_TIME > Date ‘2017-08-17’ ) AS behaviour_one
(event_name = “Receive Customer order” and END_TIME > Date ‘2017-08-17’ ) AS behaviour_two

MATCHES (^behaviour_one~>behaviour_two)