Baselight supports a rich SQL environment built on DuckDB, enabling powerful, flexible queries directly within the platform. Whether you’re joining datasets, transforming data, or running aggregations, this guide covers essential SQL commands to help you get the most out of Baselight.
Baselight uses the format @username.dataset.table to refer to tables.
All examples in this guide are runnable within Baselight.
Basics
SELECT
Returns specific columns from a dataset. Use it to control what fields appear in your result set.
SELECT year, country, population
FROM @owid.happiness.owid_happiness_2WHERE
Filters rows based on a condition. Ideal for narrowing down results.
SELECT country, population
FROM @owid.happiness.owid_happiness_2
WHERE population > 10000000 AND year = 2023ORDER BY
Sorts results by a specific column or expression. Useful for ranking or chronological order. You can use ASC and DESC to specify ascending and descending order respectively.
SELECT country, population
FROM @owid.happiness.owid_happiness_2
WHERE year = 2023
ORDER BY population DESCLIMIT
Restricts the number of rows returned. Commonly used for previews or performance.
SELECT country, population
FROM @owid.happiness.owid_happiness_2
WHERE year = 2023
ORDER BY population DESC
LIMIT 10Aggregations
COUNT(), SUM(), AVG(), MIN(), MAX()
Perform calculations across a set of values. Great for summarizing or analyzing trends.
SELECT AVG(cantril_ladder_score)
FROM @owid.happiness.owid_happiness_2
WHERE year = 2023GROUP BY
Groups rows that share a value so aggregate functions can be applied.
SELECT year, AVG(cantril_ladder_score)
FROM @owid.happiness.owid_happiness_2
GROUP BY year
ORDER BY yearHAVING
Filters aggregated results after GROUP BY.
SELECT country, AVG(cantril_ladder_score)
FROM @owid.happiness.owid_happiness_2
GROUP BY country
HAVING AVG(cantril_ladder_score) < 5
ORDER BY AVG(cantril_ladder_score) DESCJoins
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
Combine rows from two datasets based on a related column. Useful for merging complementary data.
SELECT a.country, a.population, b.gdp, b.women_parliament_seats_pct
FROM @owid.happiness.owid_happiness_2 a
INNER JOIN @kaggle.adityakishor1_all_countries_details.all_countries b
ON (a.country = b.country)
WHERE a.year = 2023
ORDER BY a.population DESCDate functions
CAST() to DATE
Converts timestamp to a plain date. Simplifies grouping or filtering by day.
SELECT CAST(a.show AS DATE) AS date
FROM @fivethirtyeight.daily_show_guests.daily_show_guests a
LIMIT 100EXTRACT()
Pulls out specific date parts like year, month, or day.
SELECT EXTRACT(year FROM a.show) AS year,
EXTRACT(month FROM a.show) AS month,
EXTRACT(day FROM a.show) AS day, raw_guest_list
FROM @fivethirtyeight.daily_show_guests.daily_show_guests a
ORDER BY year, month, day
LIMIT 100Conditional logic
CASE WHEN
Applies if/else logic within queries. Great for categorizing data.
SELECT country, population,
CASE
WHEN population >= 100000000 THEN 'Large'
WHEN population >= 10000000 THEN 'Medium'
ELSE 'Small'
END AS country_size
FROM @owid.happiness.owid_happiness_2
WHERE year = 2023Window functions
ROW_NUMBER(), RANK(), LAG(), LEAD()
Perform calculations across a “window” of rows. Use them for rankings or trend comparisons.
SELECT country, year,
ROW_NUMBER() OVER (PARTITION BY year ORDER BY cantril_ladder_score DESC) AS happiness_rank_in_year
FROM @owid.happiness.owid_happiness_2
ORDER BY country, yearCommon table expressions (CTEs)
WITH
Create temporary, named result sets to simplify complex queries.
WITH daily_volume AS (
SELECT CAST(time AS DATE) AS date,
SUM(inputamount + outputamount) AS total_volume
FROM @portals.transactions.swaps
GROUP BY date
)
SELECT *
FROM daily_volume
ORDER BY date DESCUtility features
DISTINCT
Returns only unique values in a column.
SELECT DISTINCT symbol
FROM @portals.transactions.depositsIN / NOT IN
Match values in a list. More concise than multiple OR conditions.
SELECT *
FROM @portals.transactions.deposits
WHERE symbol IN ('ETH', 'USDC')LIKE / ILIKE
Pattern matching (case-sensitive with LIKE, case-insensitive with ILIKE).
SELECT *
FROM @portals.transactions.deposits
WHERE key LIKE 'base%'Example: analyze defi swap volume
This example tracks total Ethereum-based swap volume by day, showing how simple it is to aggregate activity over time using Baselight’s built-in SQL tools.
WITH daily_volume AS (
SELECT CAST(time AS DATE) AS date,
SUM(inputamount + outputamount) AS total_volume
FROM @portals.transactions.swaps
WHERE inputtoken LIKE 'ethereum:%'
GROUP BY date
)
SELECT *
FROM daily_volume
ORDER BY date DESC