목록Data/SQL (26)
짜리몽땅 매거진
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/PzXzd/btsE6VYPoBr/AOTvaE8iqc8U45n7UwliX0/img.png)
문제1. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 출처 : 프로그래머스 SQL 고득점 kit - String, Date 문제 정보 : LEVEL 3 / 정답률 72% https://school.programmers.co.kr/learn/courses/30/lessons/164673https://school.programmers.co.kr/learn/courses/30/lessons/164671 정답 쿼리 SELECT concat("/home/grep/src/",BOARD_ID,"/",FILE_ID,FILE_NAME,FILE_EXT) AS FILE_PATH FROM USED_GOODS_FILE WHERE BOARD_ID = ( SELECT BOARD_ID FROM USED_GOODS_BOAR..
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/bUuQr1/btsEP8YkQff/aWxYJq6dctM03GBtKq0XK1/img.png)
0. 데이터 구조 주의깊게 볼만한 테이블 : ga_sess , ga_sess_hits 1. 일별 세션 건수, 일별 방문 사용자, 사용자별 평균 세션 with temp_01 as ( select to_char(date_trunc('day', visit_stime), 'yyyy-mm-dd') as d_day -- ga_sess 테이블에는 sess_id로 unique하므로 count(sess_id)와 동일 , count(distinct sess_id) as daily_sess_cnt , count(sess_id) as daily_sess_cnt_again , count(distinct user_id) as daily_user_cnt from ga.ga_sess group by to_char(date_trunc..
![](http://i1.daumcdn.net/thumb/C150x150/?fname=https://blog.kakaocdn.net/dn/bbdUql/btsEtlRuYn5/mlKZMUDXW2SUwXXRWQcpJ1/img.png)
문제1. 조건에 부합하는 중고거래 댓글 조회하기 출처 : 프로그래머스 SQL 고득점 kit - SELECT 문제 정보 : LEVEL 1 / 정답률 61% https://school.programmers.co.kr/learn/courses/30/lessons/164673 정답 쿼리 SELECT TITLE, B.BOARD_ID, REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE FROM USED_GOODS_BOARD AS A JOIN USED_GOODS_BOARD AS B ON A.BOARD_ID = B.BOARD_ID WHERE A.CREATED_DATE BETWEEN '2022-10-01' A..
DROP DATABASE IF EXISTS pokemon ; CREATE DATABASE pokemon ; USE pokemon ; CREATE TABLE mypokemon ( number INT, name VARCHAR(20), type VARCHAR(10) ); INSERT INTO mypokemon (number, name, type) VALUES (10, 'caterpie', 'bug'), (25, 'pikachu', 'electric'), (26, 'raichu', 'electric'), (133, 'eevee', 'normal'), (152, 'chikoirita', 'grass'); CREATE TABLE ability ( number INT, height FLOAT, weight FLOAT..
DROP DATABASE IF EXISTS pokemon; CREATE DATABASE pokemon; USE pokemon; CREATE TABLE mypokemon ( number int, name varchar(20), type varchar(10), attack int, defense int ); INSERT INTO mypokemon (number, name, type, attack, defense) VALUES (10, 'caterpie', 'bug', 30, 35), (25, 'pikachu', 'electric', 55, 40), (26, 'raichu', 'electric', 90, 55), (125, 'electabuzz', 'electric', 83, 57), (133, 'eevee'..
DROP DATABASE IF EXISTS pokemon; CREATE DATABASE pokemon; USE pokemon; CREATE TABLE mypokemon ( number int, name varchar(20), type varchar(10), height float, weight float ); INSERT INTO mypokemon (number, name, type, height, weight) VALUES (10, 'caterpie', 'bug', 0.3, 2.9), (25, 'pikachu', 'electric', 0.4, 6), (26, 'raichu', 'electric', 0.8, 30), (125, 'electabuzz', 'electric', 1.1, 30), (133, '..
DROP DATABASE IF EXISTS pokemon; CREATE DATABASE pokemon; USE pokemon; CREATE TABLE mypokemon ( number INT, name VARCHAR(20), type VARCHAR(10), attack INT, defense INT, capture_date DATE ); INSERT INTO mypokemon (number, name, type, attack, defense, capture_date) VALUES (10, 'caterpie', 'bug', 30, 35, '2019-10-14'), (25, 'pikachu', 'electric', 55, 40, '2018-11-04'), (26, 'raichu', 'electric', 90..
CREATE DATABASE pokemon; USE pokemon; CREATE TABLE mypokemon ( number int, name varchar(20), type varchar(20), height float, weight float, attack float, defense float, speed float ); INSERT INTO mypokemon (number, name, type, height, weight, attack, defense, speed) VALUES (10, 'caterpie', 'bug', 0.3, 2.9, 30, 35, 45), (25, 'pikachu', 'electric', 0.4, 6, 55, 40, 90), (26, 'raichu', 'electric', 0...