목록Window Function (6)

HAZEL

[SQL : 정규표현식 ] HackerRank : Weather Observation Station 7 / 8

Weather Observation Station 7 >> 문제 Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates. Input Format The STATION table is described as follows: where LAT_N is the northern latitude and LONG_W is the western longitude. >> 기본적으로 푸는 방법 SELECT DISTINCT city FROM station WHERE city LIKE '%a' OR city LIKE '%e' OR city LIKE '%i' OR city L..
DATA ANALYSIS/SQL 2021. 5. 16. 18:08
[SQL : 정규표현식 ] HackerRank : Weather Observation Station 6 / 9

Weather Observation Station 6 >> 문제 Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates. Input Format The STATION table is described as follows: where LAT_N is the northern latitude and LONG_W is the western longitude. >> 기본적으로 푸는 방법 select DISTINCT city FROM station WHERE city LIKE 'a%' OR city LIKE 'e%' OR city LIKE 'i%..
DATA ANALYSIS/SQL 2021. 5. 15. 16:59
[SQL : 서브쿼리 / INNER JOIN / WINDOW FUNCTION ] LeetCode: 185. Department Top Three Salaries

185. Department Top Three Salaries >> 문제 The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | |..
DATA ANALYSIS/SQL 2021. 5. 13. 00:08
[SQL : Window function ] SUM , MAX , ROW_NUMBER( ) ,RANK( ), DENSE_RANK( ) , LEAD, LAG

Window function 01. window function 와 groupby : groupby 함수와 유사하다. 하지만, 원래 있던 값이 아니라, 새로운 값이 나와서 그룹을 묶어주게 된다. mysql> SELECT SUM(profit) AS total_profit FROM sales; +--------------+ | total_profit | +--------------+ | 7535 | +--------------+ mysql> SELECT country, SUM(profit) AS country_profit FROM sales GROUP BY country ORDER BY country; +---------+----------------+ | country | country_profit | +..
DATA ANALYSIS/SQL 2021. 5. 12. 00:27
[SQL : INNER JOIN , WINDOW FUNCTION ] LeetCode: 180. Consecutive Numbers

180. Consecutive Numbers >> 문제 +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id is the primary key for this table. Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in any order. The query result format is in the following example: Logs table: +----+-..
DATA ANALYSIS/SQL 2021. 3. 15. 19:27
[SQL : 서브쿼리 / INNER JOIN / WINDOW FUNCTION ] LeetCode: 184. Department Highest Salary

184. Department Highest Salary >> 문제 The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1..
DATA ANALYSIS/SQL 2021. 3. 10. 18:29
이전 Prev 1 Next 다음