400 SQL queries in 90 days (extremely easy ones)

The journey starts from Friday, 9th September, 2022. (ClickHere)

1: 

Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

The CITY table is described as follows:

/*

    Enter your query here and follow these instructions:

    1. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.

    2. The AS keyword causes errors, so follow this convention: "Select t.Field From table1 t" instead of "select t.Field From table1 AS t"

    3. Type your code immediately after comment. Don't leave any blank line.

*/

Select c.NAME from CITY c Where c.COUNTRYCODE = 'USA' And c.POPULATION > 120000;

Rolling Averages/ Running Averages/ 

SELECT

   date_time,

   stock_price,

   TRUNC(AVG(stock_price)

         OVER(ORDER BY date_time ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 2)

         AS moving_average

FROM stock_values;

Say we want to obtain the running average of the number of new users registered each day. For this, we need a table with the columns day and registered_users. SQL has a concept called CTEs (common table expressions) that allows us to create a pseudo-table during query execution. We can then consume the CTE in the same query. Here’s an example query with a CTE:

WITH users_registered AS (    SELECT       date_time::date AS day,       COUNT(*)        AS registered_users    FROM    user_activity    WHERE action = 'user_registration'    GROUP BY 1 ) SELECT   day,   registered_users,   TRUNC(AVG(registered_users)         OVER(ORDER BY day ROWS BETWEEN 9 PRECEDING AND CURRENT ROW), 2)         AS moving_average_10_days,   TRUNC(AVG(registered_users)         OVER(ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)         AS moving_average_3_days FROM users_registered;

The previous query can be analyzed in two parts. In blue text, we have the CTE that generates a pseudo-table called users_registered; it contains the columns day and registered_users.

The second part of the query (in black text) is the calculation of the rolling average. Similarly to the first example, we use the AVG() window function and the clause OVER(ORDER BY day ROWS BETWEEN 9 PRECEDING AND CURRENT ROW). This applies the AVG() function to the current row and the nine rows before it. The query also calculates a running average for three days; the idea is to show both rolling average curves and compare how smooth they are.