Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
Tags
- 신경쓰기의 기술
- SQL
- forecast
- HackerRank
- leetcode
- TensorFlowGPU
- tensorflow
- 프로그래머스
- pandas profiling
- MS SQL Server
- oracle
- SKU Consolidation
- Inventory Optimization
- 파이썬
- 딥러닝
- 피그마인디언
- Labor Management System
- 웨어하우스 보관 최적화
- Product Demand
- 코딩테스트
- ModelCheckPoint
- ABC Analysis
- eda
- ProfileReport
- 당신의 인생이 왜 힘들지 않아야 한다고 생각하십니까
- kaggle
- 코딩테스트연습
- 데이터분석
- MySQL
- Gaimification
Archives
- Today
- Total
오늘도 배운다
1193. Monthly Transactions I / LeetCode, SQL, MS SQL Server 본문
코딩테스트연습(SQL)
1193. Monthly Transactions I / LeetCode, SQL, MS SQL Server
LearnerToRunner 2023. 4. 10. 23:11문제
source: LeetCode
Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.
Return the result table in any order.
제출답안(MS SQL Server)
WITH
trxs
AS(
SELECT
id, country, amount, trans_date,
FORMAT(trans_date, 'yyyy-MM') AS year_month,
CASE
WHEN state LIKE 'approved' THEN 1
ELSE 0 END is_approved,
CASE
WHEN state LIKE 'approved' THEN amount
ELSE 0 END amount_approved
FROM transactions)
SELECT
year_month AS month,
country,
COUNT(id) AS trans_count,
SUM(is_approved) AS approved_count,
SUM(amount) AS trans_total_amount,
SUM(amount_approved) AS approved_total_amount
FROM
trxs
GROUP BY
year_month, country
문제 바로가기(MS SQL Server)
728x90
'코딩테스트연습(SQL)' 카테고리의 다른 글
550. Game Play Analysis IV / LeetCode, SQL, MS SQL Server (0) | 2023.04.14 |
---|---|
1174. Immediate Food Delivery II / LeetCode, SQL, MySQL (0) | 2023.04.11 |
1070. Product Sales Analysis III / LeetCode, SQL, MySQL (0) | 2023.04.10 |
585. Investments in 2016 / LeetCode, SQL, MySQL (0) | 2023.04.10 |
570. Managers with at Least 5 Direct Reports / LeetCode, SQL, MS SQL Server (0) | 2023.04.04 |
Comments