Projects tasks consecutive sql for mac#
Projects tasks consecutive sql full#
Projects tasks consecutive sql code#
Projects tasks consecutive sql free#

WITH users (user_id, action, date) AS (VALUES (1,'start', CAST('01-01-20' AS date)), (1,'cancel', CAST('01-02-20' AS date)), (2,'start', CAST('01-03-20' AS date)), (2,'publish', CAST('01-04-20' AS date)), (3,'start', CAST('01-05-20' AS date)), (3,'cancel', CAST('01-06-20' AS date)), (1,'start', CAST('01-07-20' AS date)), (1,'publish', CAST('01-08-20' AS date))), - retrieve count of starts, cancels, and publishes for each user t1 AS ( SELECT user_id, SUM(CASE WHEN action = 'start' THEN 1 ELSE 0 END) AS starts, SUM(CASE WHEN action = 'cancel' THEN 1 ELSE 0 END) AS cancels, SUM(CASE WHEN action = 'publish' THEN 1 ELSE 0 END) AS publishes FROM users GROUP BY 1 ORDER BY 1) - calculate publication, cancelation rate for each user by dividing by number of starts, casting as float by multiplying by 1.0 (default floor division is a quirk of some SQL tools, not always needed) SELECT user_id, 1.0*publishes/starts AS publish_rate, 1.0*cancels/starts AS cancel_rate FROM t1 2.

Projects tasks consecutive sql free#

This is not needed in other implementations of SQL and is not expected in interviews.įeel free to leave your alternative answers in the comments! Note one quirk of Postgres: fractions must be multiplied by 1.0 to convert from integer to float format.

Projects tasks consecutive sql code#

The code snippets shown below can be run in PSequel as-is to yield the displayed result. I also like to follow the convention of keeping SQL operators in all caps (SELECT, FROM, WHERE, etc.), column names in lowercase (user_id, date, etc.), and simple table aliasing (t1, t2, etc.) where possible. Both approaches, however, can yield identical solutions. My preference is to use common table expressions ( CTEs) rather than nested subqueries - CTEs allow for a more linear illustration of the data wrangling sequence. Keep in mind there is usually more than one way to obtain the correct answer to a SQL problem. The questions outlined below include example solutions confirmed to work in PostgreSQL. I’ve found this to be a more satisfying learning experience. Locally executing a query, on the other hand, is instantaneous and allows for rapid iteration through syntax bugs and intermediate tables. In addition, running SQL queries in your browser can be extremely slow - the data sets are large and retrieval speed is often throttled for non-premium users. LeetCode, for instance, doesn’t support the use of window functions and hides its most interesting questions behind a paywall. Web-based SQL training resources fall short along a few dimensions. You can query against the input table using PSequel (shown above) and easily construct new tables for your own problems using this template. The first block of text in each query shown below establishes the input table and follows the format: WITH input_table (column_1, column_2) AS (VALUES (1, 'A'), (2, 'B'))

Try these queries yourself using PSequel and the input tables provided below.

Projects tasks consecutive sql for mac#

PSequel is only available for Mac - if you’re using a PC, you can try one of these Windows alternatives. You can try these out yourself by downloading PostgreSQL and PSequel (see this tutorial for a step-by-step installation guide) and then running the queries shown in the grey boxes in the text below.

Datetime operations (EXTRACT(month/day/year)).

Arithmetic operations and comparisons (+, -, *, /, //, ^,, =, !=).

Formatting (LIMIT, ORDER BY, casting as an integer, float, or date, CONCAT, COALESCE).

Window functions (RANK, DENSE_RANK, ROW_NUMBER, SUM with PARTITION BY - ORDER BY).

Conditional statements (CASE - WHEN - THEN - ELSE - END).

Projects tasks consecutive sql full#

Joins (INNER JOIN, LEFT JOIN, FULL OUTER JOIN on one or multiple (in)equalities, CROSS JOIN, UNION and UNION ALL).

Aggregation (GROUP BY with COUNT, SUM, AVERAGE).

Filtering (DISTINCT, WHERE, HAVING, AND, OR, IN, NOT IN).

Creating and aliasing (WITH, AS, GENERATE_SERIES).

These questions cover the following critical concepts:

To complement SQL training resources ( PGExercises, LeetCode, HackerRank, Mode) available on the web, I’ve compiled a list of my favorite questions that you can tackle by hand or solve with a PostgreSQL instance. Gaining working proficiency in SQL is an important prerequisite for many technology jobs and requires a bit of practice. Structured query language (SQL) is used to retrieve and manipulate data stored in relational databases.