관재탑

1. dayname(컬럼 or "날짜값")을 넣으면 날짜가 요일형식으로 나온다. SELECT DATE(event_timestamp_kst) AS dt, DAYNAME(event_timestamp_kst) AS dayname, COUNT(DISTINCT user_pseudo_id, ga_session_id) AS sessions FROM ga WHERE event_timestamp_kst BETWEEN "2022-01-22 00:00:00" AND "2022-01-28 23:59:59" GROUP BY dt, dayname ORDER BY dt 2. DATE_FORMAT(칼럼 or 날짜, "형식") 날짜를 원하는 형식으로 바꿔준다. ex) DATE_FORMAT(last_order_date, '%Y-%m-0..

SQL/MySQL 이론 2022. 9. 11. 12:03

MySQL SELECT TIMESTAMPDIFF(SECOND, last_event_timestamp, event_timestamp_kst) AS last_event, TIMESTAMPDIFF(SECOND, event_timestamp_kst, next_event_timestamp) AS next_event, TIMESTAMPDIFF(단위, 시간1, 시간2) : 시간2 - 시간1을 단위별로 계산해준다. PosgreSQL SELECT occurred_at - LAG(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event, LEAD(occurred_at, 1) OVER (PARTITION BY user_id ORDER BY ..

SQL/MySQL과 Postgre SQL의 차이점 2022. 8. 24. 21:28

https://mode.com/sql-tutorial/understanding-search-functionality/ Understanding Search Functionality | SQL Analytics Training - Mode In this lesson we'll cover: Before starting, be sure to read the overview to learn a bit about Yammer as a company. The product team is determining priorities for the next development cycle and they are considering improving the site's search functionality mode.com..

SQL/MODE project 2022. 8. 22. 21:54

https://mode.com/sql-tutorial/a-drop-in-user-engagement/ Investigating a Drop in User Engagement | SQL Analytics Training - Mode In this lesson we'll cover: Before starting, be sure to read the overview to learn a bit about Yammer as a company. Yammer's Analysts are responsible for triaging product and business problems as they come up. In many cases, these problems surface through mode.com http..

SQL/MODE project 2022. 8. 17. 17:29

https://www.hackerrank.com/challenges/weather-observation-station-6/problem?isFullScreen=true Weather Observation Station 6 | HackerRank Query a list of CITY names beginning with vowels (a, e, i, o, u). www.hackerrank.com SELECT DISTINCT city FROM station WHERE city REGEXP "^[aeiou].*" RegExp : 모음으로 시작하면서 뭐로 끝나도 상관없다. [] : 안에 하나라도 포함되고, or로 연결시켜준다고 생각하면 편하다. ^ : 바로 뒤에 오는 문자로 시작 . : 뭐가 와도 상관없음, 한..

SQL/MySQL 이론 2022. 8. 12. 16:24

https://leetcode.com/problems/department-top-three-salaries/ Department Top Three Salaries - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 문제 Table: Employee +--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | in..

SQL/MySQL 문제풀이 2022. 8. 9. 00:58

https://leetcode.com/problems/consecutive-numbers/ Consecutive Numbers - LeetCode Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview. leetcode.com 문제 Table: Logs +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ id is the prim..

SQL/MySQL 문제풀이 2022. 8. 5. 23:01

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true The Report | HackerRank Write a query to generate a report containing three columns: Name, Grade and Mark. www.hackerrank.com 문제 You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks. Grades contains the following data: Ketty gives Eve a task to generate a report containing thre..

SQL/MySQL 문제풀이 2022. 8. 5. 22:08