Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

Section 1

Preview this deck

New users

Front

1 / 39

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

1 year ago

Date created

Mar 1, 2020

Cards (39)

Section 1

(39 cards)

New users

Front

newvisits = 1

Back

Create table from existing table

Front

CREATE TABLE table_name AS ( ... )

Back

Drop null constraint

Front

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT null

Back

Landing page

Front

count(distinct(page.pagePath)) FROM ... CROSS JOIN UNNEST(hits)

Back

Assign new sessions

Front

CASE WHEN EXTRACT('EPOCH' FROM occurred_at) - EXTRACT('EPOCH' FROM last_event) >= (60 * 10) OR last_event IS NULL THEN 1 ELSE 0 END AS is_new_session

Back

Percentages with CASE and AVG

Front

AVG(CASE WHEN x THEN 1 WHEN y THEN 0...

Back

Insert Into

Front

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

Back

Add a null constraint to a new table

Front

CREATE TABLE table_name (column_name data_type not null)

Back

Create Bins

Front

WITH bins AS ( SELECT generate_series(lowest, highest, increment) AS lower, generate_series(lowest, highest, increment) AS upper) SELECT lower, upper, count(...) FROM bins LEFT JOIN ... ON ... >= lower and ... < UPPER group by lower, upper

Back

Session duration

Front

timeOnSite

Back

Where filters

Front

Cannot filter on aggregate columns, use SELECT

Back

Update Table A if something in Table B changes

Front

CREATE TABLE table_A (b_id data_type REFERENCES table_b (id) ON DELETE CASCADE)

Back

N-day inactive users

Front

LEFT JOIN AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY))

Back

choose the top five of something

Front

self-join subquery with Limit

Back

Number of total sessions

Front

COUNT(DISTINCT Concat (FullvisitorID, CAST(visitorstarttime as string))

Back

Date interval

Front

TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 20 DAY))

Back

Add unique constraint to new table (unique or primary key)

Front

CREATE TABLE table_name (column_name UNIQUE)

Back

Add a foreign key to a new table

Front

CREATE TABLE table_name (column_name data_type REFERENCES other_table (id))

Back

Bounce rate

Front

Bounces / visits

Back

Add null constraint to existing table

Front

ALTER TABLE table_name ALTER COLUMN column_name SET NOT null

Back

two columns from different tables

Front

SELECT on common column, FROM one table LEFT join other table on common column

Back

append two tables

Front

union, can add an extra column with name

Back

rownumber, rank, dense rank

Front

all, skip, multiple 2, 3 without skipping

Back

Count of sessions

Front

visitNumber

Back

Alter column type

Front

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type USING ROUND (column_name)

Back

Hits

Front

SUM(SELECT total.hits FROM UNNEST(hits))

Back

Where filters using CASE

Front

END = 'Keep' or END IS NOT NULL

Back

Global session

Front

SUM(is_new_session) OVER (ORDER BY user_id, occurred_at) AS global_session_id

Back

User

Front

fullVisitorID

Back

User session

Front

SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY occurred_at) AS user_session_id

Back

Average session length

Front

AVG(length) ( EXTRACT('SECOND' FROM MAX(occurred_at) - MIN(occurred_at)) )

Back

Create table

Front

CREATE TABLE table_name (column_1 datatype, column_2 datatype, column_3 datatype);

Back

Join except for

Front

Except, Left join Where one is null, ON !=

Back

Rename column

Front

ALTER TABLE table_name RENAME COLUMN column_name TO new_name

Back

Average Session Duration

Front

SUM(totals.timeOnSite) / COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)

Back

Add unique constraint to existing table (unique or primary key)

Front

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column_name)

Back

Bounces

Front

Total.bounces = 1 → THEN CONCAT(...)

Back

Having engaged in at least N = 4 days.

Front

HAVING COUNT(event_date) >= 4

Back

Having engaged for more than N = 0.1 minutes.

Front

SUM(event_params.value.int_value) > 0.1 60 1000000

Back