SQL for product designers

Map product concepts like 'users' and 'events' to database tables, columns, and rows.

Write a SELECT statement to retrieve specific columns from a single table (e.g., user email, signup date).

Use AS to rename columns for clearer report outputs in Metabase.

Use DISTINCT to find unique values in a column (e.g., unique event types).

Use LIMIT to sample a small number of rows for quick exploration.

Use the WHERE clause with comparison operators (=, !=, >, <) to filter for specific users or events.

Filter data for a set of values using IN (e.g., select users from a list of IDs).

Combine multiple filter conditions using AND and OR.

Filter text data using LIKE for pattern matching (e.g., find all 'button_click' events).

Handle missing data by filtering for empty values using IS NULL and IS NOT NULL.

Sort results using ORDER BY to find the most recent events or top users.

Explain the role of primary and foreign keys in linking tables like 'users' and 'events'.

Use INNER JOIN to combine rows from two tables based on a matching key (e.g., link users to their actions).

Use table aliases (AS) to simplify and clarify queries with joins.

Use LEFT JOIN to find users who have or have not performed a certain action.

Apply the WHERE clause to filter the results of a joined table.

Use COUNT() to calculate totals, such as the number of signups or daily active users.

Use AVG(), SUM(), MIN(), and MAX() to calculate summary statistics for user behavior.

Group aggregated data using GROUP BY to analyze metrics across different segments (e.g., by country, by device type).

Combine joins with aggregate functions to calculate cross-table metrics (e.g., average events per user).

Filter grouped data using HAVING to find segments that meet certain criteria (e.g., features used by more than 100 users).