In QQL Background we already explained more on how the ad-hoc database your QQL queries will be executed on is put together at run-time. This article focuses on the technical details and what feature set you can expect when writing your queries.

As a database we use Sqlite 3 (https://www.sqlite.org/). You can find detailed information on how queries work for Sqlite 3 on their website . Especially helpful for writing QQL queries is the documentation on SELECT queries. Please do note that we only support SELECT queries in our stack, as you are not meant to change any data using INSERT, UPDATE or even DELETE queries. Also all other statements except for SELECT are turned off by our system. There are also great references on available SQL functions, aggregate functions and date and time functions on their website.

Sqlite 3 also has full support for JOIN statements. These can be really powerful when it comes to joining multiple data sources within a single metric. Imagine joining data across multiple networks, e.g. comparing your engagement performance on Facebook with the same on Twitter.

Besides the default feature set of Sqlite 3, we have enhanced it by custom SQL and aggregate functions to make your life a little easier.

ZEROIFNULL(value)

...does return 0 even if value is NULL. A good example could be a formula for measuring engagement on Facebook where you want to divide by the number of posts in the given time period, e.g. (Likes+Comments+Shares)/Posts. Now if there are no posts at this day, meaning Posts will be 0, the division will be undefined, which equals NULL within Sqlite. Still, if there is no post, you probably want to define your formula to evaluate to 0 instead of NULL. Just to mention, NULL within our tool will be interpreted as “not available” and thus will also not be shown in any of the charts. Tables will show “n/a”.

Example: SELECT profileId AS dim1, time AS dim2, ZEROIFNULL((ownPostsLikes*1.0+ownPostsComments+ownPostsShares)/ownPosts) FROM facebook

PRINTFNULL(FORMAT, ...)

… exactly matches what PRINTF does ( check here and search for PRINTF), but NULL remains NULL, which will be interpreted as “not available” within our tool.

POW(base, exponent)

… returns “base” raised to the power of “exponent” (be).

MEDIAN(column)

... returns the statistical median. To find the median first the observations will be sorted by ascended. If the number of observations (n) is odd the median is the value at position ((n+1) / 2). If the number of observations (n) is even we take the values on the position (n / 2) and ((n+1) / 2) and then find the average of those two values.

FIRST(column), LAST(column)

… is an addition to the existing aggregate functions of Sqlite useful in combination with a GROUP BY statement. FIRST(...) returns the first item within the grouped item set, LAST(...) returns the last one.

Next to enhanced SQL and aggregate functions, within QQL there are certain predefined variables which will be replaced at run-time. These can be very handy when it comes to more complex queries referencing e.g. to the selected time period. Here is a full list: