Basic Queries

Example 1: Daily active users

Case

We are interested in Friktion daily active users on Solana data.

Sample Code

SELECT
  DATE(data_creation_date) AS day_,
  COUNT(DISTINCT user_address) AS users_count
FROM
  solana_mainnet.friktion_volt_instructions
WHERE
  DATE(data_creation_date) >= date_trunc('day', NOW()) - INTERVAL '{{last X days}}' DAY
GROUP BY
  1
ORDER BY
  1
LIMIT
  2000

Output

Syntax Learned

You should pay attention to the data type. In this case, data_creation_date ‘s data type is varchar, and you need to cast it as DATE before using the date functions.
You can use DISTINCT when you need to find the unique count.
With the PARAMETER feature, you can add variables to your query, allowing you to customize queries without having to touch the code. In this example, you can enter 10 and get the results from the past 10 days’ data.To learn more about how to use the PARAMETER feature, please see the SQL Query Builder.

Do not forget to use GROUP BY when you use the aggregate function like COUNT.
Remember to add ORDER BY for data that make more sense with ordered data like time-series data.

Example 2: Number of successful payments per day

Case

We are interested in the number of payments that go through successfully on Ripple every day.

Sample Code

SELECT
  DATE(data_creation_date) AS day_,
  COUNT(DISTINCT transaction_hash) payment_count
FROM
  ripple_mainnet.transactions_payment
WHERE
  DATE(data_creation_date) >= date_trunc('day', now()) - INTERVAL '{{last X days}}' DAY
  AND transaction_success = TRUE
  AND account <> destination
GROUP BY
  1
ORDER BY
  1
LIMIT
  2000

Output

Syntax Learned

Use WHERE clause to filter down the data with desired conditions. In this case, you should add transaction_success = true.
For transactions, we also do not want to include those that are transferred to itself. Therefore, you need to add account =! destination or account <> destination to exclude invalid examples.

Example 3: Top 25 tokens transfer

Case

We are interested in seeing the top 25 tokens transfer on Solana during the last 24 hours, comparing to the previous day.

Sample Code

SELECT
  new_data.token,
  new_data.symbol,
  new_data.token_transfer_count,
  new_data.total_sum_normalized,
  CAST(new_data.token_transfer_count AS DOUBLE) / day_old.token_transfer_count AS transfer_count_percent_change
FROM
  (
    SELECT
      data_creation_date,
      t.ct AS token_transfer_count,
      COALESCE(st.name, t.mint) AS token,
      log10(total_amount / POWER(10, st.decimals)) AS total_sum_normalized,
      st.symbol
    FROM
      (
        SELECT
          data_creation_date,
          mint,
          COUNT(*) AS ct,
          SUM(amount) AS total_amount
        FROM
          solana_mainnet.token_transfer
        GROUP BY
          data_creation_date,
          mint
      ) AS t
      LEFT JOIN solana_mainnet.solana_token st ON t.mint = st.address
    WHERE
      (
        st.chainid = 101
        OR st.chainid IS NULL
      )
      AND t.ct > 100
      AND st.decimals IS NOT NULL
      AND data_creation_date = date_format((now() - INTERVAL '2' DAY), '%Y-%c-%e')
    ORDER BY
      data_creation_date,
      t.ct desc
  ) new_data
  INNER JOIN (
    SELECT
      t.ct AS token_transfer_count,
      COALESCE(st.name, t.mint) AS token
    FROM
      (
        SELECT
          data_creation_date,
          mint,
          COUNT(*) AS ct,
          SUM(amount) AS total_amount
        FROM
          solana_mainnet.token_transfer
        GROUP BY
          data_creation_date,
          mint
      ) AS t
      LEFT JOIN solana_mainnet.solana_token st ON t.mint = st.address
    WHERE
      (
        st.chainid = 101
        OR st.chainid IS NULL
      )
      AND t.ct > 100
      AND st.decimals IS NOT NULL
      AND data_creation_date = date_format((now() - INTERVAL '3' DAY), '%Y-%c-%e')
    ORDER BY
      data_creation_date,
      t.ct desc
  ) day_old ON new_data.token = day_old.token
ORDER BY
  token_transfer_count desc
LIMIT
  25

Output

Syntax Learned

Nested Query's a common syntax. This may looks intimidating the logic is fairly simple. You can use the following template to write any nested tables.

SELECT
  column_1,
  column_2,
  COUNT(1) AS COUNT
FROM
  (
    SELECT
      t1.column_1,
      t2.column_2,
    FROM
      schema.table_1 t1
      INNER JOIN schema.table_2 t2 ON t1.column_3 = t2.column_4
    WHERE
      data_creation_date = '2022-4-14'
  ) temp_table
GROUP BY
  1,
  2
ORDER BY
  1 DESC
LIMIT
  100

Example 4: Cumulative users

Case

We want to know the number of Friktion’s cumulative users.

Sample Code

SELECT
  SUBSTRING(
    CAST(
      date_trunc('day', CAST("date_time" AS TIMESTAMP)) AS VARCHAR
    ),
    1,
    10
  ) AS "__timestamp",
  SUM("cumulative_users") AS cumulative_users
FROM
  (
    WITH
      dnu AS (
        WITH
          t AS (
            SELECT
              user_address,
              date_trunc('day', TIMESTAMP) date_time
            FROM
              solana_mainnet.friktion_volt_instructions
            GROUP BY
              1,
              2
          )
        SELECT
          user_joined_date date_time,
          COUNT(1) new_users
        FROM
          (
            SELECT
              user_address,
              MIN(date_time) user_joined_date
            FROM
              t
            GROUP BY
              user_address
          )
        GROUP BY
          1
      )
    SELECT
      date_time,
      SUM(new_users) OVER (
        ORDER BY
          date_time ROWS unbounded preceding
      ) cumulative_users
    FROM
      dnu
  ) AS "virtual_table"
GROUP BY
  date_trunc('day', CAST("date_time" AS TIMESTAMP))
ORDER BY
  1
LIMIT
  10000

Output

Syntax Learned

Use CTE expressions such as the WITH clause here to select the daily data first and add them over time. The clause is used for defining a temporary relation such that the output of this temporary relation is available and is used by the query that is associated with the WITH clause. You can start using the WITH clause with this following template, and learn more about it in the materials recommended in the SQL Guide.

WITH
  expression_name [(column_name [,...])]
AS (CTE_definition) 
SQL_statement

Example 5: Successful vs failed transactions (%)

Case

We are interested in seeing the successful vs failed transactions ratio on Ripple.

Sample Code

WITH
  successful_txns AS (
    SELECT
      DATE_FORMAT(date_trunc('day', ledger_time), '%m/%d/%Y') AS day_,
      COUNT(DISTINCT transaction_hash) success_count
    FROM
      ripple_mainnet.transactions
    WHERE
      ledger_time >= date_trunc('day', now()) - INTERVAL '{{last X days}}' DAY
      AND DATE(data_creation_date) <= DATE_ADD('day', -1, CURRENT_DATE)
      AND transaction_success = TRUE
    GROUP BY
      1
  ),
  failed_txns AS (
    SELECT
      DATE_FORMAT(date_trunc('day', ledger_time), '%m/%d/%Y') AS day_,
      COUNT(DISTINCT transaction_hash) fail_count
    FROM
      ripple_mainnet.transactions
    WHERE
      ledger_time >= date_trunc('day', NOW()) - INTERVAL '{{last X days}}' DAY
      AND DATE(data_creation_date) <= DATE_ADD('day', -1, CURRENT_DATE)
      AND transaction_success = FALSE
    GROUP BY
      1
  )
SELECT
  s.day_,
  s.success_count,
  f.fail_count
FROM
  successful_txns s
  INNER JOIN failed_txns f ON s.day_ = f.day_
ORDER BY
  1
LIMIT
  10000

Output

Syntax Learned

To have multiple WITH clauses, you do not need to specify WITH multiple times. You can write a multiple WITH clause using the following syntax template.

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
FROM    cte1