SQL Basic
The basic operations here are from codecademy SQL course
Basic Operations
SELECT * FROM celebs;
CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);
INSERT INTO celebs VALUES (1, 'Justin Bieber', 21);
UPDATE celebs SET age = 3 WHERE id = 1;
ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;
Drop and create
---- Create new table
DROP TABLE if exists some_temp_table;
CREATE TABLE some_temp_table AS
SELECT some_col FROM another_table;
---- Insert table with data partition
INSERT overwrite TABLE some_table partition(dt='${day}')
Tables UNION
SELECT *
FROM ( SELECT A FROM table_1 )
UNION( SELECT A FROM table_2)
Make Histogram
SELECT round(data,1) AS bucket, COUNT(*) AS cnt
FROM data_table
GROUP BY round(data,1)
Add Row Numbers
SELECT row_number()
OVER (partition BY some_attr ORDER BY another_attr) AS rn
Get Next/Last Row
SELECT (LEAD (another_attr, 1)
OVER (PARTITION BY another_attr ORDER BY rn)) as next_row
SELECT (LAG (another_attr, 1)
OVER (PARTITION BY another_attr ORDER BY rn)) as last_row
Parse JSON
SELECT parse_json_object(some_json_obj, 'inner_index') FROM some_table
Generate Constant Values
SELECT ones.n + 10*tens.n AS test_id
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)
WHERE ones.n + 10*tens.n BETWEEN 1 AND 100
ORDER BY 1
Select Random Row
SELECT * FROM data_table
ORDER BY RAND(unix_timestamp())
LIMIT 1