Common Mistakes

In this section, we have collected some common SQL mistakes and their resolutions. For correct examples, please refer to Example Queries for Web3 Data.

Example 1: Don’t narrow down the select scope via WHERE

As some tables are really big and include billions of rows, it’s better to narrow down the select scope with the WHERE clause. A common WHERE condition will be specifying the search dates. Please see the Example Queries for Web3 Datato look for more examples about how to specify it with a date column such as data_creation_date.

Wrong SQL Example:

This SQL query without the data_creation_date condition will take a much longer processing time than it supposes to.

SELECT
  column_1,
  column_2,
  COUNT(1) AS COUNT
FROM
  database.table
GROUP BY
  1,
  2
ORDER BY
  1 DESC
LIMIT
  100 

Correct SQL Example:

SELECT
  column_1,
  column_2,
  COUNT(1) AS COUNT
FROM
  database.table
WHERE
  data_creation_date = '2022-7-14'
GROUP BY
  1,
  2
ORDER BY
  1 DESC
LIMIT
  100 

Example 2: Wrong date format

The correct date format should be ‘yyyy-mm-dd’ (instead of ‘yyyy-m-d’).

Cautions: Unlike other data, the Solana data is currently using the date format of ‘yyyy-m-d’, and it will be changed to ‘yyyy-mm-dd’ soon.

Wrong SQL Example:

This query will return 0 rows because data_creation_date’s format is wrongly specified as ‘2022-07-04 instead of ‘2022-7-4’.

SELECT
  column_1,
  column_2
FROM
    database.table
WHERE
    data_creation_date = '2022-07-04'
LIMIT 100

Correct SQL Example:

SELECT
    column_1,
    column_2
FROM
    database.table
WHERE
    data_creation_date = '2022-7-4'
LIMIT 100

Example 3: Missing DATABASE name

Please always include the DATABASE name with the TABLE name. If you want to see a detailed explanation of the difference among DATABASE, and TABLE, please go to the Query Builder.

Wrong SQL Example:

SELECT
  column_1,
  column_2,
FROM
  TABLE 

Correct SQL Example:

SELECT
  column_1,
  column_2
FROM
  database.table 

Example 4: Incorrect wrapping of DATABASE and TABLE

You do not need to wrap the name of DATABASE and TABLE.

Wrong SQL Example:

SELECT
  column_1,
  column_2
FROM
  `database`.`table` 

Correct SQL Example:

SELECT
  column_1,
  column_2
FROM
  database.table 

Example 5: Select TABLE name as COLUMN name

Only column names are selectable.

Wrong SQL Example:

SELECT
    table_name
FROM
    database.table

If you want to see all the info from the COLUMNS, please use ‘*’ instead of the TABLE name.

Correct SQL Example:

SELECT
  COLUMN
FROM
  database.table 

Correct SQL Example:

SELECT
  *
FROM
  database.table 

Example 6: Incorrect Wrapping the Value

For a column value, you should wrap it with a single quote Don’t wrap it with a double quote or not wrap it at all.

Mistake: Use a double quote to wrap the value:

Wrong SQL Example:

SELECT
    column
FROM
    database.table
WHERE
    column = "some_value"

Mistake: Not wrap the value at all:

Wrong SQL Example:

SELECT
    column
FROM
    database.table
WHERE
    column = some_value

Correct SQL Example:

SELECT
  COLUMN
FROM
  database.table
WHERE
  COLUMN = 'some_value' 

Example 7: Missing GROUP BY

If you want to find the COUNT for each category, please add a GROUP BY at the end.

Wrong SQL Example:

SELECT
  category,
  COUNT(*) AS count_for_each_category
FROM
  database.table 

This SQL will bring an error because you cannot use aggregate functions in the WHERE clause.
What you should do is use the HAVING clause.

Wrong SQL Example:

SELECT
  category,
  COUNT(*) AS count_for_each_category
FROM
  database.table
WHERE
  COUNT(*) > 10
GROUP BY
  category; 

Correct SQL Example:

SELECT
  category,
  COUNT(*) AS count_for_each_category
FROM
  database.table
GROUP BY
  category 
HAVING COUNT (*) > 10

Through the examples, we have seen the most common mistakes that people often make. By practicing writing SQL, you will quickly learn from your own mistakes!