A toolbox to build elementary charts on the WebApp using SQL. For reasons of confidentiality, the data has been anonymized.

Scorecard

Displays one value to show an overall level of performance.
Example of use cases : Total sales, Number of visits, Number of active sales representatives…

# 1-dimension chart : the query needs to return 1 value

SELECT
ROUND(SUM(total_product_value),0) as x
FROM
  w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)
SQL

Gauge chart

Displays how an actual level of performance operates in comparison to the budgeted.

The value can be either a simple number either a percentage.
You can define a target outside the SQL query.

Example of use cases : Point of sales coverage, active users vs registered users…

# 1-dimension chart : the query needs to return 1 value

# Gauge - example with a random figure

SELECT
nb_outlets_visited
FROM
  w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)

# Gauge - example with a percentage

# Using Common Table Expressions, create a first table to count the number of active users 
# Assign it the name table1

WITH table1 AS (
  SELECT
    COUNT(DISTINCT user_id) as active_users
  FROM
    log
LEFT JOIN mobile_users ON mobile_users.id=log.user_id 
  WHERE
    user_type = 'MOBILE'
    AND action_code = 'CHECK_IN'
    AND system_action IS NOT true
 AND $date_filter:bi_timestamp$
AND (email IS NULL OR email NOT LIKE '%optimetriks%')
    AND user_id IN (
      SELECT
        user_id
      FROM
        teams_mobile_users
      WHERE
        $teams_filter:team_id$
    )
),

# Create a second table to count the number of registered users
# Assign it the name table2

table2 AS (
  SELECT
    COUNT(DISTINCT user_id) as registered_users
  FROM
    teams_mobile_users
LEFT JOIN mobile_users ON mobile_users.id=teams_mobile_users.user_id 
  WHERE
    $teams_filter:team_id$
AND licensed IS true
AND (email IS NULL OR email NOT LIKE '%optimetriks%')
)

# Compute the percentage of active users among the registered users

SELECT

CASE WHEN registered_users > 0 
THEN 100 * active_users / registered_users ELSE 0 END

from
  table1,
  table2
SQL

Horizontal bar chart and bar chart

Look at a specific value across different categories.
Example of use cases : Number of orders per brand, sales per product…

# 2-dimensions chart : the query needs to return 2 columns 
  named x (value on the x axis) and y (value on the y axis)
  No other column name will be accepted.

# X values can be either text or date.
# Always make sure you have the correct format selected.

SELECT

name as x,
nb_items as y

FROM w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)

GROUP BY 1
ORDER BY 2 DESC
SQL

Pie chart

Displays relationships of parts to a whole.
Example of use cases : % of presence of one item…

# 2-dimensions chart : the query needs to return 2 columns 
  named value (here: 31, 66) and label (here: Visible, Non visible)
  No other column name will be accepted.

SELECT

nb_items as value,
name as label

FROM w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)

SQL

Line chart

Emphasizes the overall shape of an entire series of values over time.
Example of use cases : Point of sales visited over time, Sales over time….

# 2-dimensions chart : the query needs to return 2 columns 
  named x (value on the x axis) and y (value on the y axis)
  No other column name will be accepted.

# X values can only be date

SELECT

_completed_at as x,
nb_items as y

FROM w_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id
WHERE
(email is null or email not like '%optimetriks%') AND
  $date_filter:_completed_at$
  AND sale <> 'Non'
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)

GROUP BY 1
ORDER BY 2 DESC
SQL

Stacked bar chart

Look at a specific value across different categories.  
Example of use cases : Total sales, Number of visits, Number of active sales representatives…

METHOD 1 : using the sub-queries

# CREATE THREE DISTINCT SUBQUERIES FOR THE THREE DISTINCT CATEGORIES
# each query needs to return 2 columns 
  named x (value on the x axis) and y (value on the y axis)
  No other column name will be accepted.



# SUBQUERY 1 - Select the name of the category and assign it the name x,
# Select the specific value to display and assign it the name y 
 
SELECT
_brand as x,
COUNT(DISTINCT CASE WHEN past_sales_sku='Il y a moins de 6 mois' THEN _place_id END) as y
FROM l_sku
LEFT JOIN w_id_past_sales ON l_sku._id = w_id_past_sales._object_id
LEFT JOIN w_id ON w_id._id=w_id_past_sales._submission_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id

WHERE l_sku._actif is true AND $date_filter:_started_at$ AND (email not like '%optimetriks%' OR email is null )
GROUP BY _brand

# SUBQUERY 2 - Select the name of the category and assign it the name x,
# Select the specific value to display and assign it the name y 

SELECT
_brand as x, 
COUNT(DISTINCT CASE WHEN past_sales_sku='Il y a plus de 6 mois' THEN _place_id END) as y

FROM l_sku
LEFT JOIN w_id_past_sales ON l_sku._id = w_id_past_sales._object_id
LEFT JOIN w_id ON w_id._id=w_id_past_sales._submission_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id

WHERE l_sku._actif is true AND $date_filter:_started_at$ AND (email not like '%optimetriks%' OR email is null )
GROUP BY _brand



# SUBQUERY 3 - Select the name of the category and assign it the name x,
# Select the specific value to display and assign it the name y 

SELECT
_brand as x,
COUNT(DISTINCT CASE WHEN past_sales_sku='Jamais' THEN _place_id END) as y
FROM l_sku
LEFT JOIN w_id_past_sales ON l_sku._id = w_id_past_sales._object_id
LEFT JOIN w_id ON w_id._id=w_id_past_sales._submission_id
LEFT JOIN mobile_users ON mobile_users.id=w_id._user_id

WHERE l_sku._actif is true AND $date_filter:_started_at$ AND (email not like '%optimetriks%' OR email is null )
GROUP BY _brand



METHOD 2 : using the row query and the main query
# ROW QUERY
# the row query needs to return 2 columns 
  named label (value displaying on the dashboard) and 
  value (value stored in the $row_query_result$)
  No other column name will be accepted.
# The main query needs to return 2 columns
named x (value on the x axis) and y (value on the y axis)
  No other column name will be accepted.
The link between the main query and the row query is done in  the WHERE statement 
by referring to the row query result with the expression $row_query_result$
  
For example to get the activity in the log per user

# ROW QUERY

SELECT
  CONCAT(first_name, ' ', last_name) as label,
  id as value
from
  mobile_users
LEFT JOIN (SELECT COUNT(id) as activity, user_id FROM log_all 
WHERE $date_filter:timestamp$ GROUP BY user_id) t ON mobile_users.id=t.user_id
WHERE activity > 0


# MAIN QUERY

SELECT
  count(DISTINCT id) as y,
action_code as x
FROM
  log_all
WHERE
  user_id IN ($row_query_result$)
  AND $date_filter:timestamp$
AND (action_code='CREATE_SUBMISSION' OR action_code='RUN_DASHBOARD')
GROUP BY x

SQL

Table

Displays a grid that contains related data in a logical series of rows and columns.
Example of use cases : Total sales, Number of visits, Number of active sales representatives…

# Multiple dimensions chart: it can returns many columns.
  The column name will display as header in the table

# Create "table1" to select all the fields to display in the table

WITH table1 AS (
  SELECT
    _user_id,
    _user_name,
    _place_id,
    _id,
    total_product_value,
    CASE
      WHEN sale = 'Oui' THEN 1
      ELSE 0
    END AS success,
    _completed_at
  FROM
    w_id
  WHERE
    $date_filter:_completed_at$
AND _user_id IN (SELECT user_id FROM teams_mobile_users WHERE $teams_filter:team_id$)
)

# Compute the required indicators for the table and group by the "active user" field (for this case) 

SELECT
  DISTINCT _user_name as active_users,
  COUNT(DISTINCT _place_id) as "clients_visited",
  COUNT(_id) as visits,
  SUM(success) as "# Sales",
  100 * SUM(success) / COUNT (_id) as "%Conversion",
  ROUND(SUM(total_product_value), 2) as sales
FROM
  table1
GROUP BY
  _user_name;
SQL

Displays a library of pictures with a label for each picture

# 1 dimension chart
  The query must return 1 column named url and containing the url for the pictures
  

SELECT
url
FROM uploads
WHERE $date_filter:created_at$
SQL

Matrix

Displays a table with several dimensions

There are several ways to build a Matrix.
The easiest way is described below but if there is any need to use another method, please refer to the workshop dedicated to this component (Link)

Multi dimensions chart - Use this method
  The column and row pivots must be set up as NONE.
  The query must return at least 1 column with the values (names can be custom)
  + row_label (name of the row) + row_value (data displayed)
  + column_label (name of the column) + column_value (data displayed)

SELECT

COUNT(log_all.id)  as actions,
CONCAT(first_name, ' ', last_name) as row_value,
'User' as row_label,
to_char(timestamp, 'dd/mm/yy') as column_value,
'Date' as column_label

FROM log_all

LEFT JOIN mobile_users ON mobile_users.id = log_all.user_id
LEFT JOIN teams_mobile_users ON teams_mobile_users.user_id=log_all.user_id
LEFT JOIN teams ON teams.id=team_id

WHERE $date_filter:timestamp$
GROUP BY 2,3,4,5
SQL


A custom hierarchy can be created within the SQL query.

ulti dimensions chart - Use this method
  The column and row pivots must be set up as NONE.
  The query must return at least 1 column with the values (names can be custom)
  + row_label (name of the row) + row_value (data displayed)
  + column_label (name of the column) + column_value (data displayed)
  + hierarchy levels for row/columns under this format : 

For the row hierarchy

row_0_label
row_0_value
…
row_n_label
row_n_value

For the column hierarchy

column_0_label
column_0_value
…
column_n_label
column_n_value


SELECT

COUNT(log_all.id)  as actions,
CONCAT(first_name, ' ', last_name) as row_value,
'User' as row_label,
teams.name as row_0_value,
'Team' as row_0_label,
to_char(timestamp, 'dd/mm/yy') as column_value,
'Date' as column_label

FROM log_all

LEFT JOIN mobile_users ON mobile_users.id = log_all.user_id
LEFT JOIN teams_mobile_users ON teams_mobile_users.user_id=log_all.user_id
LEFT JOIN teams ON teams.id=team_id

WHERE $date_filter:timestamp$
GROUP BY 2,3,4,5,6,7
SQL

You can’t have null values in the custom pivots. If you have some null values, you will get a Something went wrong error.