Ngôn ngữ truy vấn có cấu trúc (SQL) được sử dụng để truy xuất và thao tác dữ liệu được lưu trữ trong cơ sở dữ liệu quan hệ. Đạt được thành thạo làm việc trong SQL là điều kiện tiên quyết quan trọng đối với nhiều công việc công nghệ và…

< font style = "vertical-align: inherit;"> Bạn đang xem: bài tập thực hành sql cho người mới bắt đầu

Vui lòng để lại câu trả lời thay thế của bạn trong các nhận xét!

Các đoạn mã hiển thị bên dưới có thể được chạy trong PSequel nguyên trạng để mang lại kết quả được hiển thị. Lưu ý một điều khác của Postgres: phân số phải được nhân với 1,0 để chuyển đổi từ định dạng số nguyên sang số thực. Điều này không cần thiết trong các triển khai khác của SQL và không được mong đợi trong các cuộc phỏng vấn.

Các câu hỏi được nêu bên dưới bao gồm các giải pháp ví dụ được xác nhận là hoạt động trong PostgreSQL. Hãy nhớ rằng thường có nhiều cách để có được câu trả lời chính xác cho một vấn đề SQL. Sở thích của tôi là sử dụng các biểu thức bảng thông thường (CTE) hơn là các truy vấn con lồng nhau – CTE cho phép minh họa tuyến tính hơn về trình tự quấn dữ liệu. Tuy nhiên, cả hai cách tiếp cận đều có thể mang lại các giải pháp giống hệt nhau. Tôi cũng muốn tuân theo quy ước giữ các toán tử SQL trong tất cả các chữ hoa (SELECT, FROM, WHERE, v.v.), tên cột ở dạng chữ thường (user_id, ngày, v.v.) và bí danh bảng đơn giản (t1, t2, v.v.) nếu có thể.

Tài nguyên đào tạo SQL dựa trên web bị thiếu hụt theo một số khía cạnh. Ví dụ: LeetCode không hỗ trợ việc sử dụng các chức năng cửa sổ và ẩn những câu hỏi thú vị nhất của nó đằng sau một bức tường phí. Ngoài ra, việc chạy các truy vấn SQL trong trình duyệt của bạn có thể cực kỳ chậm – tập dữ liệu lớn và tốc độ truy xuất thường bị hạn chế đối với người dùng không cao cấp. Mặt khác, thực thi cục bộ một truy vấn là tức thời và cho phép lặp lại nhanh chóng thông qua các lỗi cú pháp và bảng trung gian. Tôi thấy đây là một trải nghiệm học tập hài lòng hơn.

Bạn có thể truy vấn bảng nhập liệu bằng PSequel (hiển thị ở trên) và dễ dàng tạo bảng mới cho các vấn đề của riêng bạn bằng cách sử dụng mẫu này.

Khối văn bản đầu tiên trong mỗi truy vấn được hiển thị bên dưới thiết lập bảng đầu vào và tuân theo định dạng:

Bạn có thể tự mình thử những thứ này bằng cách tải xuống PostgreSQL và PSequel (xem hướng dẫn này để biết từng bước- hướng dẫn cài đặt bước) và sau đó chạy các truy vấn được hiển thị trong các hộp màu xám trong văn bản bên dưới. PSequel chỉ khả dụng cho Mac – nếu bạn đang sử dụng PC, bạn có thể thử một trong các lựa chọn thay thế Windows này.

Những câu hỏi này bao gồm các khái niệm quan trọng sau:

Để bổ sung cho việc đào tạo SQL tài nguyên (PGExercises, LeetCode, HackerRank, Mode) có sẵn trên web, tôi đã biên soạn danh sách các câu hỏi yêu thích của mình mà bạn có thể giải quyết bằng tay hoặc giải quyết bằng phiên bản PostgreSQL.

Ngôn ngữ truy vấn có cấu trúc (SQL ) được sử dụng để truy xuất và thao tác dữ liệu được lưu trữ trong cơ sở dữ liệu quan hệ. Đạt được trình độ làm việc thành thạo trong SQL là điều kiện tiên quyết quan trọng đối với nhiều công việc công nghệ và đòi hỏi bạn phải thực hành một chút.

Những câu hỏi và giải pháp ví dụ này sẽ giúp các kỹ năng của bạn luôn sắc bén.

Câu hỏi

1. Tỷ lệ hủy

Từ bảng ID người dùng, hành động và ngày tháng sau, hãy viết một truy vấn để trả về tỷ lệ xuất bản và hủy cho mỗi người dùng.

 < p class = "gc og lq jk ox b do oy oz l pa" id = "5b15"> VỚI người dùng (user_id, action, date) 
AS (VALUES
(1, 'start', CAST ('01 -01-20 'AS ngày)),
(1,' hủy ', CAST ('01 -02-20' AS ngày)),
(2, 'start', CAST ('01 -03-20 'AS ngày)),
(2,' xuất bản ', CAST ('01 -04-20' AS ngày)),
(3, 'start', CAST ('01 -05-20 'AS ngày)),
(3,' hủy ', CAST ('01 -06-20' AS ngày)),
(1, 'start', CAST ('01 -07-20 'AS ngày)),
(1,' xuất bản ', CAST ('01 -08-20' AS ngày))),

- truy xuất số lần bắt đầu, hủy và xuất bản cho mỗi người dùng

t1 AS (< br /> SELECT
user_id,
SUM (CASE WHEN action = 'start' THEN 1 ELSE 0 END) AS bắt đầu,
SUM (CASE WHEN action = 'hủy' THÌ 1 ELSE 0 END ) AS hủy bỏ,
SUM (CASE WHEN action = 'Publishers' SAU 1 LẦN 0 KẾT THÚC) AS xuất bản
TỪ người dùng
NHÓM THEO 1
ĐƠN HÀNG THEO 1)

- tính toán tỷ lệ xuất bản, hủy cho mỗi người dùng bằng cách chia cho số lần bắt đầu, truyền dưới dạng float bằng cách nhân với 1.0 (phân chia tầng mặc định là một điểm khác biệt của một số công cụ SQL, không phải lúc nào cũng cần thiết)

SELECT
user_id,
1.0 * xuất bản / bắt đầu AS Publishing_rate,
1.0 * hủy / bắt đầu AS hủy_tỷ lệ
TỪ t1

2. Những thay đổi về giá trị ròng

Từ bảng giao dịch sau giữa hai người dùng, hãy viết một truy vấn để trả lại thay đổi về giá trị ròng cho mỗi người dùng, được sắp xếp theo thứ tự giảm dần thay đổi thực.

 

VỚI giao dịch (người gửi, người nhận, số tiền, ngày giao dịch)
NHƯ (GIÁ TRỊ
( 5, 2, 10, CAST ('2-12-20' AS ngày)),
(1, 3, 15, CAST ('2-13-20' AS ngày)),
( 2, 1, 20, CAST ('2-13-20' AS ngày)),
(2, 3, 25, CAST ('2-14-20' AS date)),
( 3, 1, 20, CAST ('2-15-20' AS ngày)),
(3, 2, 15, CAST ('2-15-20' AS ngày)),
( 1, 4, 5, CAST ('2-16-20' AS ngày))),

- tổng số tiền cho mỗi người gửi (ghi nợ) và người nhận (tín dụng)

ghi nợ AS (
CHỌN người gửi,
SUM (số tiền) NHƯ đã ghi nợ
TỪ các giao dịch
NHÓM BẰNG 1),

ghi có AS (
CHỌN
người nhận,
SUM (số tiền) NHƯ được ghi có
TỪ các giao dịch
NHÓM THEO 1) < / p>

- đầy đủ (bên ngoài) tham gia các bảng ghi nợ và tín dụng trên id người dùng, lấy thay đổi ròng làm chênh lệch giữa tín dụng và ghi nợ, ép buộc giá trị null thành số không với liên kết ()

CHỌN
THAN (người gửi, người nhận) AS người dùng,
COALESCE (ghi có, 0) - COALESCE (ghi nợ, 0) AS net_change
TỪ ghi nợ d
FULL THAM GIA tín dụng c
BẬT d.sender = c.receiver
ĐẶT HÀNG BẰNG 2 MÔ TẢ

3. Các mục thường xuyên nhất

Từ bảng sau chứa danh sách ngày và các mặt hàng được đặt hàng, hãy viết một truy vấn để trả về mặt hàng thường xuyên nhất được đặt hàng vào mỗi ngày. Trả lại nhiều mặt hàng trong trường hợp bị ràng buộc.

 

VỚI mục (ngày, mục)
AS (VALUES
(CAST ('01 -01-20 'AS date),' apple '),
(CAST ('01-01 -20 'AS date),' apple '),
(CAST ('01 -01-20' AS date), 'lê'),
(CAST ('01-01-20 'AS ngày), 'lê'),
(CAST ('01-02-20 'AS ngày),' lê '),
(CAST ('01-02-20' AS ngày), ' lê '),
(CAST ('01 -02-20' ngày AS), 'lê'),
(CAST ('01 -02-20 'ngày AS),' cam ')) ,

- thêm cột đếm mục vào bảng hiện có, nhóm theo ngày và cột mục < / p>

t1 AS (
CHỌN
ngày,
mục,
COUNT (*) AS item_count
FROM items
GROUP BY 1, 2
ORDER BY 1),

- thêm cột xếp hạng theo thứ tự giảm dần, phân vùng theo ngày

t2 AS (
SELECT
*,
RANK () OVER (PARTITION BY date ORDER BY item_count DESC) AS date_rank
TỪ t1)

- trả về tất cả các ngày và mục trong đó rank = 1 < / p>

CHỌN
ngày,
mục
TỪ t2
WHERE date_rank = 1

4. Chênh lệch thời gian giữa các hành động mới nhất

Từ bảng hành động của người dùng sau đây, hãy viết một truy vấn để trả lại cho từng người dùng khoảng thời gian đã trôi qua giữa hành động cuối cùng và hành động thứ hai đến cuối cùng, theo thứ tự tăng dần theo ID người dùng.

< pre class = "la lb lc ld gz ov bt ow">

VỚI người dùng (user_id, action, action_date)
AS (VALUES
(1, ‘start’, CAST (‘2-12-20’ AS ngày)),
(1, ‘hủy bỏ’, CAST (‘2-13-20’ AS ngày )),
(2, ‘start’, CAST (‘2-11-20’ AS date)),
(2, ‘Publishers’, CAST (‘2-14-20’ AS date )),
(3, ‘start’, CAST (‘2-15-20’ AS ngày)),
(3, ‘hủy’, CAST (‘2-15-20’ AS ngày )),
(4, ‘start’, CAST (‘2-18-20’ AS date)),
(1, ‘Publishers’, CAST (‘2-19-20’ AS date ))),

– tạo cột xếp hạng ngày, được phân vùng theo ID người dùng, sử dụng hàm cửa sổ ROW_NUMBER ()

t1 AS (
CHỌN
*,
ROW_NUMBER () HẾT (PHẦN THEO LỆNH CỦA user_id BY action_date DESC) NHƯ date_rank
TỪ người dùng),

– lọc theo cột xếp hạng ngày để lấy các hành động mới nhất và tiếp theo từ bảng này

AS mới nhất (
CHỌN *
TỪ t1
WHERE date_rank = 1),

next_latest AS (
SELECT *
FROM t1
WHERE date_rank = 2)

– nối trái vào hai bảng này, trừ phần mới nhất cho phần gần nhất để có thời gian trôi qua

CHỌN
l1.user_id,
l1.action_date – l2.action_date AS days_elapsed
TỪ l1
TRÁI THAM GIA next_latest l2
BẬT l1.user_id = l2 .user_id
ĐẶT HÀNG THEO 1

5. Người dùng siêu cấp

Một công ty xác định người dùng siêu cấp của mình là những người đã thực hiện ít nhất hai giao dịch. Từ bảng sau, hãy viết một truy vấn để trả về, cho mỗi người dùng, ngày họ trở thành siêu người dùng, theo thứ tự đầu tiên của người dùng lớn tuổi nhất. Người dùng không phải là người dùng cấp cao cũng nên có mặt trong bảng.

 

VỚI người dùng (user_id, product_id, transaction_date)
AS (VALUES
(1, 101, CAST ('2-12-20' AS ngày)),
( 2, 105, CAST ('2-13-20' AS ngày)),
(1, 111, CAST ('2-14-20' AS date)),
(3, 121, CAST ('2-15-20' AS ngày)),
(1, 101, CAST ('2-16-20' AS ngày)),
(2, 105, CAST ('2 -17-20 'AS ngày)),
(4, 101, CAST (' 2-16-20 'AS date)),
(3, 105, CAST (' 2-15-20 'AS date))),

- tạo cột số giao dịch bằng cách sử dụng ROW_NUMBER (), phân vùng theo ID người dùng

t1 AS (
SELECT
*,
ROW_NUMBER () HẾT (PHẦN THEO ĐƠN ĐẶT HÀNG CỦA user_id THEO transaction_date) AS transaction_number
TỪ người dùng),

- lọc kết quả ta được ble on transaction_number = 2

t2 AS (
SELECT
user_id,
transaction_date
FROM t1
WHERE transaction_number = 2),

- bên trái tham gia cùng người dùng cấp cao vào bảng người dùng đầy đủ, đặt hàng theo ngày

t3 AS (
CHỌN DISTINCT user_id
TỪ người dùng)

CHỌN
t3.user_id,
transaction_date NHƯ superuser_date
TỪ t3
THAM GIA TRÁI t2
TRÊN t3.user_id = t2.user_id
ĐẶT HÀNG BẰNG 2

6. Nội dung đề xuất (khó)

Sử dụng hai bảng sau, viết một truy vấn để trả lại các đề xuất trang cho người dùng mạng xã hội dựa trên các trang mà bạn bè của họ đã thích nhưng họ chưa đánh dấu là đã thích. Thứ tự kết quả bằng cách tăng dần ID người dùng. Nguồn .

 

VỚI bạn bè (user_id, friend)
NHƯ (GIÁ TRỊ
(1, 2), (1, 3), ( 1, 4), (2, 1), (3, 1), (3, 4), (4, 1), (4, 3)),

lượt thích (user_id, page_likes)
AS (VALUES
(1, 'A'), (1, 'B'), (1, 'C'), (2, 'A'), (3, 'B'), (3, 'C'), (4, 'B')),

< p class = "gc og lq jk ox b do po pp pq pr ps oz l pa" id = "65e5"> - bạn bè tham gia nội bộ và bảng thích trang trên user_id

t1 AS (
SELECT
l.user_id,
l.page_likes,
f.friend < br /> TỪ lượt thích l
THAM GIA kết bạn f
BẬT l.user_id = f.user_id),

- còn lại lượt thích tham gia vào điều này, yêu cầu người dùng = bạn bè và người dùng thích = lượt thích bạn bè

t2 AS (
SELECT
t1.user_id,
t1.page_likes,
t1.friend,
l.page_likes AS friend_likes
TỪ t1
THAM GIA TRÁI lượt thích l
BẬT t1.friend = l.user_id
VÀ t1.page_likes = l.page_likes)

- nếu một cặp bạn bè không có chung lượt thích trang, cột lượt thích bạn bè sẽ trống - hãy kéo các mục này ra

CHỌN DISTINCT
bạn bè AS user_id,
page_likes AS suggest_page
FROM t2
NƠI friend_likes LÀ KHÔNG ĐỦ
ĐẶT HÀNG CỦA 1

7. Khách truy cập trên thiết bị di động và web

Với hai bảng sau, trả về tỷ lệ người dùng chỉ truy cập thiết bị di động, chỉ truy cập web và truy cập cả hai.

 

VỚI di động (user_id, page_url)
AS (VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'A'), (9, 'B'), (2, 'C'), (10, 'B')),

web (user_id, page_url)
AS (VALUES
(6, 'A' ), (2, 'B'), (3, 'C'), (7, 'A'), (4, 'B'), (8, 'C'), (5, 'B')) ,


- tham gia bên ngoài người dùng web và thiết bị di động trên ID người dùng

t1 AS (
CHỌN DISTINCT
m.user_id AS mobile_user,
w.user_id AS web_user
TỪ di động m
FULL THAM GIA web w
BẬT m. user_id = w.user_id)

- tính một phần của chỉ di động, chỉ web, và cả giá trị trung bình (một và không) được chỉ định trong điều kiện câu lệnh chữ hoa chữ thường

CHỌN
AVG (TRƯỜNG HỢP KHI mobile_user KHÔNG ĐẦY ĐỦ VÀ web_user KHÔNG ĐỦ THÌ 1 LẦN 0 KẾT THÚC) NHƯ mobile_fraction,
AVG (TRƯỜNG HỢP KHI web_user KHÔNG ĐỦ VÀ mobile_user KHÔNG ĐỦ THÌ 1 PHÉP 0 KẾT THÚC) NHƯ web_fraction,
AVG (TRƯỜNG HỢP KHI web_user KHÔNG ĐỦ VÀ mobile_user KHÔNG ĐẦY ĐỦ THÌ 1 LẦN 0 KẾT THÚC) NHƯ both_fraction
TỪ t1

8. Tỷ lệ nâng cấp theo hành động của sản phẩm (khó)

Với hai bảng sau, trả về phần người dùng, được làm tròn đến hai chữ số thập phân, những người đã truy cập tính năng hai (nhập: F2 trong bảng sự kiện) và được nâng cấp lên cao cấp trong vòng 30 ngày đầu tiên kể từ ngày ký lên.

 

VỚI người dùng (user_id, name , join_date)
AS (VALUES
(1, 'Jon', CAST ('2-14-20' AS date)),
(2, 'Jane', CAST ('2 -14-20 'AS ngày)),
(3,' Jill ', CAST (' 2-15-20 'AS date)),
(4,' Josh ', CAST (' 2 -15-20 'AS ngày)),
(5,' Jean ', CAST (' 2-16-20 'AS date)),
(6,' Justin ', CAST (' 2 -17-20 'AS date)),
(7,' Jeremy ', CAST (' 2-18-20 'AS date))),

sự kiện (user_id, type, access_date)
AS (VALUES
(1, 'F1', CAST ('3-1- 20 'AS ngày)),
(2,' F2 ' , CAST ('3-2-20' AS ngày)),
(2, 'P', CAST ('3-12-20' AS date)),
(3, 'F2' , CAST ('3-15-20' AS ngày)),
(4, 'F2', CAST ('3-15-20' AS date)),
(1, 'P' , CAST ('3-16-20' AS date)),
(3, 'P', CAST ('3-22-20' AS date))),


- lấy người dùng tính năng 2 và ngày truy cập tính năng 2 của họ

t1 AS (
SELECT
user_id,
type,
access_date AS f2_date
TỪ sự kiện
WHERE type = 'F2'),

- nhận phí bảo hiểm người dùng và ngày nâng cấp cao cấp của họ

t2 AS (
CHỌN
user_id,
type,
access_date AS premium_date
FROM sự kiện
WHERE type = 'P'),

- đối với mỗi người dùng 2 tính năng, có thời gian từ khi tham gia đến khi nâng cấp cao cấp de (hoặc null nếu không nâng cấp) bằng cách tham gia bên trong bảng người dùng đầy đủ với tính năng 2 người dùng trên ID người dùng và bỏ tham gia người dùng cao cấp trên ID người dùng, sau đó trừ ngày nâng cấp cao cấp cho ngày tham gia

t3 AS (
CHỌN t2.premium_date - u.join_date AS lift_time
TỪ người dùng u
THAM GIA t1 < br /> BẬT u.user_id = t1.user_id
TRÁI THAM GIA t2
BẬT u.user_id = t2.user_id)

- tính toán phần người dùng có thời gian nâng cấp dưới 30 ngày dưới dạng trung bình của các giá trị (giá trị một và số không) được chỉ định trong điều kiện câu lệnh chữ hoa, làm tròn đến hai chữ số thập phân

CHỌN
ROUND (AVG (TRƯỜNG HỢP KHI NÂNG CẤP_time & lt; 30 THEN 1 ELSE 0 END), 2) AS lift_rate
FROM t3

9. Kết bạn nhiều nhất

Cho bảng sau, trả về danh sách người dùng và số bạn bè tương ứng của họ. Sắp xếp kết quả theo số lượng bạn bè giảm dần và trong trường hợp hòa, bằng cách tăng dần ID người dùng. Giả sử rằng chỉ những tình bạn duy nhất được hiển thị
(tức là [1, 2] sẽ không hiển thị lại với tên [2, 1]). Từ LeetCode .

 

VỚI bạn bè (user1, user2)
AS (VALUES (1, 2), (1, 3), (1, 4 ), (2, 3)),


- biên dịch tất cả các lần xuất hiện của người dùng thành một cột, giữ nguyên các mục nhập trùng lặp với UNION ALL

t1 AS (
CHỌN người dùng1 NHƯ user_id
TỪ bạn bè
ĐOÀN KẾT TẤT CẢ
CHỌN user2 AS user_id
TỪ bạn bè)

- nhóm theo ID người dùng, đếm tất cả các lần xuất hiện của người dùng đó

CHỌN
user_id,
COUNT (*) AS friend_count
TỪ t1
NHÓM THEO 1
ĐẶT HÀNG BẰNG 2 MÔ TẢ

10. Tổng hợp dự án (khó)

Bảng dự án chứa ba cột: task_id, start_date và end_date. Sự khác biệt giữa end_date và start_date là 1 ngày cho mỗi hàng trong bảng. Nếu ngày kết thúc nhiệm vụ liên tiếp nhau thì chúng là một phần của cùng một dự án. Các dự án không trùng lặp.

Viết truy vấn để trả về ngày bắt đầu và ngày kết thúc của mỗi dự án và số ngày cần để hoàn thành. Sắp xếp theo thời lượng dự án tăng dần và ngày bắt đầu tăng dần trong trường hợp ràng buộc. Từ HackerRank .

 

VỚI dự án (task_id, start_date, end_date)
AS (VALUES
(1, CAST ('10-01- 20 'AS date), CAST ('10 -02-20' AS date)),
(2, CAST ('10 -02-20 'AS date), CAST ('10-03-20' AS date date)),
(3, CAST ('10 -03-20 'AS date), CAST ('10 -04-20' AS date)),
(4, CAST ('10 - 13-20 'AS date), CAST ('10 -14-20' AS date)),
(5, CAST ('10 -14-20 'AS date), CAST ('10-15-20 'AS date)),
(6, CAST (' 10-28-20 'AS date), CAST ('10-29-20' AS date)),
(7, CAST (' 10-30-20 'AS date), CAST ('10-331-20' AS date))),

- lấy ngày bắt đầu không có trong cột ngày kết thúc (đây là ngày bắt đầu dự án" đúng ")

t1 AS (
CHỌN start_date
TỪ dự án
Ở đâu start_date KHÔNG VÀO (SELECT end_date F Dự án ROM)),

- lấy ngày kết thúc không có trong cột ngày bắt đầu (đây là “True” ngày kết thúc dự án)

t2 AS (
SELECT end_date
TỪ dự án
TẠI ĐÂU end_date KHÔNG VÀO (CHỌN ngày bắt đầu TỪ dự án)),

- lọc các cặp đầu-cuối hợp lý (bắt đầu & lt; end), sau đó tìm ngày kết thúc chính xác cho mỗi ngày bắt đầu (ngày kết thúc tối thiểu, vì không có dự án nào trùng lặp)

t3 AS (
SELECT
start_date,
MIN (end_date) AS end_date
FROM t1, t2
WHERE start_date & lt; end_date
NHÓM THEO 1)

CHỌN
*,
end_date - start_date AS project_duration
FROM t3
ORDER BY 3, 1

11. Tham dự sinh nhật

Cho những điều sau hai bảng, viết truy vấn để trả về phân số của học sinh, được làm tròn đến hai chữ số thập phân, những người đã đi học
(điểm danh = 1) vào ngày sinh nhật của họ. Nguồn .

 

CÓ điểm danh (học sinh, ngày học, chuyên cần)
NHƯ (GIÁ TRỊ
(1, CAST ('2020-04-03) 'AS date), 0),
(2, CAST (' 2020-04-03 'AS date), 1),
(3, CAST (' 2020-04-03 'AS date) , 1),
(1, CAST ('2020-04-04' AS date), 1),
(2, CAST ('2020-04-04' AS date), 1),
(3, CAST ('2020-04-04' AS date), 1),
(1, CAST ('2020-04-05' AS date), 0),
(2, CAST ('2020-04-05' AS date), 1),
(3, CAST ('2020-04-05' AS date), 1),
(4, CAST ('2020-04-05' AS date), 1)),

sinh viên (student_id , school_id, grade_level, date_of_birth)
AS (VALUES
(1, 2, 5, CAST ('2012-04-03' AS date)),
(2, 1, 4, CAST ('2013-04-04' AS date)),
(3, 1, 3, CAST ('2014-04-05' AS date)),
(4, 2, 4, CAST ('2013-04-03' AS date)))

- tham gia bảng điểm danh và học sinh trên mã số học sinh, và ngày và tháng của ngày học = ngày và tháng sinh nhật, lấy giá trị trung bình của cột chuyên cần và làm tròn < / p>

CHỌN VÒNG (AVG (tham dự), 2) AS birthday_attendance
TỪ tham dự a < br /> THAM GIA sinh viên s
TRÊN a.student_id = s.student_id
AND EXTRACT (MONTH FROM school_date) = EXTRACT (MONTH FROM date_of_birth)
AND EXTRACT (DAY FROM school_date) = EXTRACT ( NGÀY TỪ date_of_birth)

12. Điểm tin tặc

Cho hai bảng sau, hãy viết một truy vấn để trả về ID hacker, tên và tổng điểm (tổng điểm tối đa cho mỗi thử thách đã hoàn thành) được sắp xếp theo điểm giảm dần và theo ID hacker tăng dần trong trường hợp điểm hòa . Không hiển thị các mục dành cho tin tặc với số điểm bằng không. Từ HackerRank .

 

VỚI tin tặc (hacker_id, name)
AS (VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Joe'),
(4, 'Jim')),

lần gửi (submit_id, hacker_id, challenge_id, điểm)
AS (VALUES
(101, 1, 1, 10),
( 102, 1, 1, 12),
(103, 2, 1, 11),
(104, 2, 1, 9),
(105, 2, 2, 13) ,
(106, 3, 1, 9),
(107, 3, 2, 12),
(108, 3, 2, 15),
(109, 4, 1, 0)),

- từ bảng gửi, nhận điểm tối đa cho mỗi cặp thử thách hacker

t1 AS (
CHỌN
hacker_id,
challenge_id,
MAX (điểm) NHƯ max_score
TỪ bài nộp
NHÓM THEO 1, 2)

- tham gia nội dung này với bảng tin tặc, tổng hợp tất cả điểm tối đa, bộ lọc để loại trừ tin tặc có tổng điểm bằng 0 và sắp xếp kết quả theo tổng điểm và tin tặc ID

CHỌN
t1.hacker_id,
h.name, < br /> SUM (t1.max_score) AS total_score
TỪ t1
THAM GIA tin tặc h
TRÊN t1.hacker_id = h.hacker_id
NHÓM THEO 1, 2
CÓ SUM (max_score) & gt; 0
ĐẶT HÀNG BẰNG 3 MÔ TẢ, 1

13. Xếp hạng không có RANK (khó)

Viết truy vấn để xếp hạng điểm trong bảng sau mà không cần sử dụng hàm cửa sổ. Nếu có sự hòa giữa hai điểm số, cả hai phải có cùng thứ hạng. Sau khi hòa, thứ hạng sau phải là giá trị số nguyên liên tiếp tiếp theo. Từ LeetCode .

 

CÓ điểm (id, điểm)
NHƯ (GIÁ TRỊ
(1, 3,50),
(2 , 3,65),
(3, 4,00),
(4, 3,85),
(5, 4,00),
(6, 3,65))

- tự tham gia vào bất đẳng thức tạo ra một bảng có một điểm và tất cả các điểm lớn bằng điểm này đều được kết hợp với nó , nhóm theo id và điểm đầu tiên, đồng thời đếm tất cả các giá trị duy nhất của điểm đã kết hợp sẽ mang lại giá trị tương đương với DENSE_RANK () [kiểm tra kết quả tham gia để hiểu]

CHỌN
s1.score,
COUNT (DISTINCT s2.score) AS score_rank
FROM điểm s1
JOIN điểm s2
TRÊN s1.score & lt; = s2.score
NHÓM THEO s1.id, s1.score
ĐẶT HÀNG THEO 1 MÔ TẢ

14. Tổng lương tích lũy

Bảng sau chứa thông tin lương hàng tháng của một số nhân viên. Viết một truy vấn để nhận, cho mỗi tháng, tổng số tiền tích lũy của tiền lương của một nhân viên trong khoảng thời gian 3 tháng, không bao gồm tháng gần đây nhất. Kết quả phải được sắp xếp theo ID nhân viên tăng dần và tháng. Từ LeetCode .

 

VỚI nhân viên (id, pay_month, lương)
AS (VALUES
(1, 1, 20),
(2, 1, 20),
(1, 2, 30),
(2, 2, 30),
(3, 2, 40),
(1, 3, 40),
(3, 3, 60),
(1, 4, 60),
(3, 4, 70)),

< p class = "gc og lq jk ox b do po pp pq pr ps oz l pa" id = "1548">
- thêm cột cho thứ hạng tháng giảm dần (tháng gần nhất = 1) cho mỗi nhân viên

t1 AS (
SELECT *,
RANK () OVER (PHẦN THEO id ĐẶT HÀNG THEO pay_month DESC) AS month_rank
TỪ nhân viên)

- bộ lọc để loại trừ tháng và tháng gần nhất từ ​​5+ trở lên, tạo tổng tiền lương tích lũy bằng hàm SUM () làm hàm cửa sổ, sắp xếp theo ID và tháng

CHỌN id
,
pay_month,
tiền lương,
SUM (tiền lương) HẾT (PHẦN BẰNG id ĐẶT HÀNG THEO month_rank DESC) NHƯ luỹ kế_sum
TỪ t1
WHERE month_rank! = 1
AND month_rank & lt ; = 4
ĐẶT HÀNG THEO 1, 2

15. Bảng xếp hạng các đội

Viết truy vấn để trả về điểm số của mỗi đội trong bảng đội sau tất cả các trận đấu được hiển thị trong bảng trận đấu. Điểm được thưởng như sau: 0 điểm nếu thua, 1 điểm nếu hòa và 3 điểm nếu thắng. Kết quả phải bao gồm tên và điểm của đội và được sắp xếp theo thứ tự giảm dần điểm. Trong trường hợp hòa, hãy sắp xếp theo tên đội được xếp theo thứ tự bảng chữ cái.

 

VỚI các đội (team_id, team_name)
NHƯ (VALUES
(1, 'New York'),
(2, 'Atlanta'),
(3, 'Chicago'),
(4, 'Toronto'),
(5, 'Los Angeles'),
(6, 'Seattle')),

trận đấu (match_id, host_team, guest_team, host_goals, guest_goals)
NHƯ (GIÁ TRỊ
(1, 1, 2, 3, 0),
(2, 2, 3, 2, 4),
(3, 3, 4, 4, 3),
(4, 4, 5, 1, 1),
(5, 5, 6, 2, 1),
(6, 6, 1, 1, 2)),


- thêm điểm chủ nhà và cột điểm khách vào bảng đối sánh, sử dụng trường hợp-khi-thì để tính điểm giành chiến thắng , ràng buộc và thua lỗ

t1 AS (
CHỌN
*,
TRƯỜNG HỢP KHI host_goals & gt; guest_goals THÌ 3
KHI host_goals = guest_goals THÌ 1
ELSE 0 KẾT THÚC LÀ host_points,
TRƯỜNG HỢP KHI host_goals & lt; guest_goals THÌ 3
KHI host_goals = guest_goals THÌ 1
HẾT 0 KẾT THÚC KHI guest_points
TỪ các trận đấu)

- tham gia kết quả vào bảng các đội hai lần để cộng điểm cho mỗi đội với tư cách là đội chủ nhà và đội khách, sau đó đặt hàng theo yêu cầu

SELECT
t.team_name,
a.host_points + b.guest_points AS total_points
FROM các đội t
THAM GIA t1 a
BẬT t.team_id = a.host_team
THAM GIA t1 b
BẬT t.team_id = b.guest_team
ĐẶT HÀNG BẰNG 2 MÔ TẢ, 1

16. Những khách hàng không mua sản phẩm

Từ sau bảng, viết một truy vấn để hiển thị ID và tên của những khách hàng đã mua sản phẩm A và B, nhưng không mua sản phẩm C, được đặt hàng theo ID khách hàng tăng dần.

 

VỚI khách hàng (id, name)
AS (VALUES
(1, 'Daniel' ),
(2, 'Diana'),
(3, 'Elizabeth'),
(4, 'John')),

đơn đặt hàng (order_id, customer_id, product_name)
AS (VALUES
(1, 1, 'A'), < br /> (2, 1, 'B'),
(3, 2, 'A'),
(4, 2, 'B'),
(5, 2, 'C'),
(6, 3, 'A'),
(7, 3, 'A'),
(8, 3, 'B'),
(9, 3, 'D'))


- tham gia cùng khách hàng và các bảng đơn đặt hàng trên ID khách hàng, lọc những người đã mua cả sản phẩm A và B, loại bỏ những người đã mua sản phẩm C, trả về các cột ID và tên được sắp xếp theo ID tăng dần

CHỌN DISTINCT
id,
tên
TỪ đơn đặt hàng o
THAM GIA khách hàng c
BẬT o.customer_id = c.id
WHERE customer_id IN (CHỌN customer_id
TỪ đơn hàng
WHERE product_name = 'A')
VÀ customer_id IN (CHỌN customer_id
TỪ đơn đặt hàng
TẠI ĐÂY product_name = 'B')
VÀ customer_id KHÔNG VÀO (CHỌN customer_id
TỪ đơn đặt hàng
WHERE product_name = 'C')
ĐẶT HÀNG THEO 1

17. Vĩ độ trung vị (cứng)

Viết truy vấn để trả về vĩ độ trung bình của các trạm thời tiết từ mỗi tiểu bang trong bảng sau, làm tròn đến phần mười độ gần nhất. Lưu ý rằng không có hàm MEDIAN () trong SQL! Từ HackerRank .

 

CÓ trạm (id, thành phố, tiểu bang, vĩ độ, kinh độ)
AS (VALUES
(1, 'Asheville' , 'North Carolina', 35,6, 82,6),
(2, 'Burlington', 'North Carolina', 36,1, 79,4),
(3, 'Chapel Hill', 'North Carolina', 35,9 , 79.1),
(4, 'Davidson', 'North Carolina', 35.5, 80.8),
(5, 'Elizabeth City', 'North Carolina', 36.3, 76.3),
(6, 'Fargo', 'North Dakota', 46.9, 96.8),
(7, 'Grand Forks', 'North Dakota', 47.9, 97.0),
(8, 'Hettinger' , 'North Dakota', 46.0, 102.6),
(9, 'Inkster', 'North Dakota', 48.2, 97.6)),


- chỉ định số hàng theo thứ tự vĩ độ cho mỗi tiểu bang và nhận tổng số hàng cho mỗi tiểu bang

t1 AS (
SELECT
*,
ROW_NUMBER () OVE R (PHẦN THEO tiểu bang ĐẶT HÀNG THEO vĩ độ ASC) AS row_number_state,
count (*) OVER (PARTITION BY state) AS row_count
TỪ trạm)

- lọc đến hàng giữa (cho tổng số hàng lẻ) hoặc hai hàng giữa (cho tổng số hàng chẵn), sau đó lấy giá trị trung bình của những hàng đó, nhóm theo trạng thái

CHỌN
trạng thái,
AVG (vĩ độ) AS median_latitude
TỪ t1
WHERE row_number_state & gt; = 1,0 * row_count / 2
AND row_number_state & lt; = 1.0 * row_count / 2 + 1
GROUP BY 1

18. Các thành phố được ngăn cách tối đa

Từ cùng một bảng ở câu hỏi 17, hãy viết truy vấn để trả về cặp thành phố được phân tách xa nhất cho mỗi tiểu bang và khoảng cách tương ứng (tính bằng độ, làm tròn đến 2 chữ số thập phân) giữa hai thành phố đó. Từ HackerRank .

 

CÓ trạm (id, thành phố, tiểu bang, vĩ độ, kinh độ)
AS (VALUES
(1, 'Asheville' , 'North Carolina', 35,6, 82,6),
(2, 'Burlington', 'North Carolina', 36,1, 79,4),
(3, 'Chapel Hill', 'North Carolina', 35,9 , 79.1),
(4, 'Davidson', 'North Carolina', 35.5, 80.8),
(5, 'Elizabeth City', 'North Carolina', 36.3, 76.3),
(6, 'Fargo', 'North Dakota', 46.9, 96.8),
(7, 'Grand Forks', 'North Dakota', 47.9, 97.0),
(8, 'Hettinger' , 'North Dakota', 46.0, 102.6),
(9, 'Inkster', 'North Dakota', 48.2, 97.6)),


- tự tham gia vào các tiểu bang và thành phố phù hợp & lt; thành phố (tránh các cặp thành phố giống hệt nhau và được tính kép), trạng thái kéo, cặp thành phố và tọa độ vĩ độ / kinh độ cho mỗi thành phố

t1 AS (
CHỌN
s1.state,
s1.city AS city1,
s2.city AS city2,
s1.latitude AS city1_lat,
s1.longitude AS city1_long,
s2.latitude AS city2_lat,
s2.longitude AS city2_long
TỪ các trạm s1
THAM GIA các trạm s2
TRÊN s1.state = s2.state
VÀ s1.city & lt; s2.city),

- thêm cột hiển thị khoảng cách Euclid làm tròn

t2 AS (
SELECT *,
ROUND (((city1_lat - city2_lat) ^ 2 + (city1_long - city2_long) ^ 2) ^ 0,5, 2) AS khoảng cách
TỪ t1),

- xếp hạng từng cặp thành phố theo khoảng cách giảm dần cho mỗi tiểu bang

t3 AS (
SELECT *, RANK () OVER (PARTITION BY state ORDER BY distance DESC) AS dist_rank
FROM t2) < / p>

- trả về cặp thành phố với sự phân tách tối đa

CHỌN
bang,
thành phố1,
thành phố2,
khoảng cách
TỪ t3
WHERE dist_rank = 1

19. Thời gian chu kỳ

Viết truy vấn để trả về thời gian chu kỳ trung bình qua mỗi tháng. Thời gian chu kỳ là thời gian trôi qua giữa một người dùng tham gia và những người được mời của họ tham gia. Những người dùng tham gia mà không có lời mời sẽ có số 0 trong cột “được mời bởi”.

 

VỚI người dùng (user_id, join_date, bring_by)
AS (VALUES
(1, CAST ('01-01-20 'AS date), 0),
(2, CAST ('01-10-20 'AS ngày), 1),
(3, CAST ('02-05-20' AS date), 2),
(4 , CAST ('02-12-20 'AS ngày), 3),
(5, CAST ('02-25-20' AS date), 2),
(6, CAST (' 03-01-20 'AS ngày), 0),
(7, CAST ('03-01-20' AS date), 4),
(8, CAST ('03-04- 20 'AS date), 7)),


- tự tham gia trên ID người dùng được mời, trích xuất tháng tham gia từ ngày tham gia của người mời và tính toán thời gian chu kỳ làm chênh lệch giữa ngày tham gia của người mời và người được mời

t1 AS (
SELECT
CAST (EXTRACT (MONTH FROM u2.join_date) AS int) AS tháng,
u1.join_date - u2.join_date AS cycle_time
TỪ người dùng u1
JO TRONG người dùng u2
BẬT u1.invited_by = u2.user_id)

- nhóm theo tháng tham gia, lấy trung bình số lần chu kỳ trong mỗi tháng

CHỌN
tháng,
AVG (cycle_time) AS cycle_time_month_avg
TỪ t1
NHÓM THEO 1
ĐẶT HÀNG THEO 1

20. Ba liên tiếp

Bảng tham dự ghi lại số người được tính trong một đám đông mỗi ngày một sự kiện được tổ chức. Viết truy vấn để trả về một bảng hiển thị ngày và số lượng khách truy cập trong các khoảng thời gian tham gia nhiều, được định nghĩa là ba mục nhập liên tiếp (không nhất thiết là các ngày liên tiếp) với hơn 100 khách truy cập. Từ LeetCode .

 

CÓ sự tham dự (ngày_sự kiện, khách truy cập)
AS (VALUES
(CAST ('01-01-20 'AS ngày ), 10),
(CAST ('01 -04-20 'AS date), 109),
(CAST ('01 -05-20' AS date), 150),
(CAST ('01-06-20 'AS date), 99),
(CAST ('01-07-20' AS date), 145),
(CAST ('01-08 -20 'AS ngày), 1455),
(CAST ('01 -11-20' AS date), 199),
(CAST ('01-12-20 'AS date), 188 )),

- thêm số hàng để xác định các mục nhập liên tiếp, kể từ ngày cột có một số khoảng trống

t1 AS (
SELECT *,
ROW_NUMBER () HẾT (ĐẶT HÀNG THEO event_date) NHƯ ngày_năm
TỪ việc tham dự),

- - lọc điều này để loại trừ các ngày có & gt; 100 khách truy cập

t2 AS (
CHỌN *
TỪ t1
Khách truy cập ở đâu & gt; 100),

- tự tham gia (bên trong) hai lần vào offset = 1 ngày và offset = 2 ngày

t3 AS (
CHỌN
a.day_num AS day1,
b .day_num AS day2,
c.day_num AS day3
TỪ t2 a
THAM GIA t2 b
ON a.day_num = b.day_num - 1
THAM GIA t2 c < br /> ON a.day_num = c.day_num - 2)

- kéo ngày và số lượng khách truy cập cho những ngày liên tiếp hiển thị trong bảng trước đó

CHỌN
event_date,
khách truy cập
TỪ t1
TRONG ĐÓ ngày_năm IN (CHỌN ngày1 TỪ t3)
HOẶC ngày_năm VÀO (CHỌN ngày 2 TỪ t3)
HOẶC day_num TRONG (CHỌN ngày 3 TỪ t3)

21. Thường được mua cùng nhau

Sử dụng hai bảng sau, viết một truy vấn để trả về tên và tần suất mua của ba cặp sản phẩm hàng đầu thường được mua cùng nhau nhất. Tên của cả hai sản phẩm phải xuất hiện trong một cột. Nguồn .

 

VỚI đơn hàng (order_id, customer_id, product_id)
AS (VALUES
(1, 1, 1),
(1, 1, 2),
(1, 1, 3),
(2, 2, 1),
(2, 2, 2),
( 2, 2, 4),
(3, 1, 5)),

sản phẩm (id, tên)
NHƯ (GIÁ TRỊ
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E')),

- nhận các cặp sản phẩm duy nhất từ ​​cùng một đơn đặt hàng bằng cách tự ghép bảng đơn đặt hàng trên ID đơn đặt hàng và ID sản phẩm & lt; ID sản phẩm (tránh các cặp sản phẩm giống hệt nhau và được tính hai lần)

t1 AS (
CHỌN
o1.product_id AS prod_1,
o2.product_id AS prod_2
TỪ đơn hàng o1
THAM GIA đơn hàng o2
BẬT o1.order_id = o2.order_id
VÀ o1.product_id & lt; o2.product_id),

- nối bảng sản phẩm vào bảng này để lấy tên sản phẩm, nối để có các cặp sản phẩm trong một cột

t2 AS (
CHỌN CONCAT (p1.name, '', p2.name) AS product_pair
TỪ t1
THAM GIA sản phẩm p1
ON t1.prod_1 = p1.id
THAM GIA sản phẩm p2
ON t1.prod_2 = p2.id)

- nhóm theo cặp sản phẩm, trả về 3 mục hàng đầu được sắp xếp theo tần suất mua hàng

SELECT *,
COUNT (*) AS buy_freq
FRO M t2
NHÓM THEO 1
ĐẶT HÀNG BẰNG 2 MÔ TẢ
GIỚI HẠN 3

22. Hiệu quả điều trị trung bình (khó)

Từ bảng tóm tắt kết quả của một nghiên cứu sau đây, hãy tính hiệu quả điều trị trung bình cũng như giới hạn trên và giới hạn dưới của khoảng tin cậy 95%. Làm tròn các số này đến 3 chữ số thập phân.

 

VỚI nghiên cứu (người tham gia, bài tập, kết quả)
NHƯ (GIÁ TRỊ
(1, 0, 0),
(2, 1, 1),
(3, 0, 1 ),
(4, 1, 0),
(5, 0, 1),
(6, 1, 1),
(7, 0, 0),
(8, 1, 1),
(9, 1, 1)),


- lấy kết quả trung bình, độ lệch chuẩn và quy mô nhóm cho các nhóm điều trị và kiểm soát

điều khiển AS (
CHỌN
AVG (kết quả) AS avg_outcome,
STDDEV (kết quả) AS std_dev,
COUNT (*) AS group_size
FROM study
WHERE task = 0),

xử lý AS (
CHỌN
AVG (kết quả) AS avg_outcome,
STDDEV (kết quả) AS std_dev,
COUNT (*) AS group_size
TỪ nghiên cứu
Nhiệm vụ WHERE = 1) ,

- lấy kích thước hiệu quả điều trị trung bình

effect_size AS (
CHỌN t.avg_outcome - c.avg_outcome AS effect_size
TỪ đối chứng c, xử lý t),

- xây dựng khoảng tin cậy 95% bằng cách sử dụng z * = 1,96 và độ lớn của các lỗi tiêu chuẩn riêng lẻ [std dev / sqrt (cỡ mẫu)]

conf_interval AS (
SELECT 1.96 * (t.std_dev ^ 2 / t.group_size
+ c.std_dev ^ 2 / c.group_size) ^ 0,5 AS conf_int
TỪ xử lý t, kiểm soát c)

CHỌN vòng (es.effect_size, 3) AS point_estimate,
vòng (es.effect_size - ci.conf_int, 3) AS Lower_bound,
round (es.effect_size + ci .conf_int, 3) AS upper_bound
FROM effect_size es, conf_interval ci

23. Tổng số tiền lương

Bảng sau đây cho biết mức lương hàng tháng của một nhân viên trong chín tháng đầu tiên trong một năm nhất định. Từ đó, hãy viết một truy vấn để trả về một bảng hiển thị, cho mỗi tháng trong nửa đầu năm, tổng số lương của nhân viên trong tháng đó và hai tháng tiếp theo, được sắp xếp theo thứ tự thời gian.

 

CÓ lương (tháng, lương)
NHƯ (GIÁ TRỊ < br /> (1, 2000),
(2, 3000),
(3, 5000),
(4, 4000),
(5, 2000), < br /> (6, 1000),
(7, 2000),
(8, 4000),
(9, 5000))

- tự tham gia để khớp tháng n với tháng n, n + 1 và n + 2, sau đó tính tổng tiền lương những tháng đó, lọc theo nửa đầu năm và sắp xếp

SELECT
s1. tháng,
SUM (s2.salary) NHƯ lương_3mos
TỪ lương s1
THAM GIA lương s2
TRÊN s1.month & lt; = s2.month
VÀ s1.month & gt; s2.month - 3
NHÓM THEO 1
CÓ s1.month & lt; 7
ĐẶT HÀNG BẰNG 1

24. Tỷ lệ hủy taxi

Từ bảng người dùng và chuyến đi đã cho cho dịch vụ taxi, hãy viết truy vấn để trả về tỷ lệ hủy chuyến trong hai ngày đầu tiên của tháng 10, được làm tròn đến hai chữ số thập phân, đối với các chuyến đi không liên quan đến người đi xe hoặc tài xế bị cấm. Từ LeetCode .

 

CÓ chuyến đi (trip_id, rider_id, driver_id, status, request_date)
AS (VALUES
(1, 1, 10 , 'complete', CAST ('2020-10-01' AS date)),
(2, 2, 11, 'cancelling_by_driver', CAST ('2020-10-01' AS date)),
(3, 3, 12, 'complete', CAST ('2020-10-01' AS date)),
(4, 4, 10, 'cancelling_by_rider', CAST ('2020-10-02 'AS date)),
(5, 1, 11,' complete ', CAST (' 2020-10-02 'AS date)),
(6, 2, 12,' complete ', CAST ('2020-10-02' AS date)),
(7, 3, 11, 'complete', CAST ('2020-10-03' AS date))),

người dùng (user_id, bị cấm, loại)
AS (VALUES
(1, 'no' , 'rider'),
(2, 'yes', 'rider'),
(3, 'no', 'rider'),
(4, 'no', ' người lái xe '),
(10,' không ',' người lái xe '),
(11,' không ',' người lái xe '),
(12,' không ',' người lái xe ' ))

- lọc bảng chuyến đi để loại trừ những người lái và tài xế bị cấm, sau đó tính toán tỷ lệ hủy chuyến là 1 - phần số chuyến đi đã hoàn thành, lọc thành hai ngày đầu tiên của tháng
CHỌN
request_date,
1 - AVG (CASE WHEN status = 'complete' THEN 1 ELSE 0 END) AS hủy_mục
TỪ chuyến đi
WHERE rider_id NOT IN (CHỌN user_id
TỪ người dùng
WHERE bị cấm = 'yes')
VÀ driver_id KHÔNG VÀO (CHỌN user_id
TỪ người dùng
WHERE bị cấm = 'yes')
NHÓM THEO 1
CÓ CHIẾT KHẤU (NGÀY TỪ request_date) & lt; = 2

25. Đường cong duy trì (cứng)

Từ bảng hoạt động của người dùng sau, hãy viết truy vấn để trả về phần người dùng được giữ lại (hiển thị một số hoạt động) trong một số ngày nhất định sau khi tham gia. Theo quy ước, người dùng được coi là đang hoạt động vào ngày tham gia của họ (ngày 0).

 

VỚI người dùng (user_id, action_date, action)
AS (VALUES
(1, CAST ('01-20 'AS date),' Tham gia '), < br /> (1, CAST ('01 -02-20 'AS ngày),' Truy cập '),
(2, CAST ('01 -02-20' AS ngày), 'Tham gia'), < br /> (3, CAST ('01 -02-20 'AS ngày),' Tham gia '),
(1, CAST ('01 -03-20' AS ngày), 'Truy cập'), < br /> (3, CAST ('01 -03-20 'AS date),' Access '),
(1, CAST ('01 -04-20' AS date), 'Access')),

- lấy ngày tham gia cho từng người dùng

join_dates AS (
SELECT
user_id,
action_date AS join_date
FROM users
WHERE action = 'Tham gia'),

- tạo vectơ chứa tất cả các ngày trong phạm vi ngày

date_vector AS (
CHỌN CAST (GENERATE_SERIES (MIN (action_date)), MAX ( action_date),
'1 ngày' :: khoảng thời gian) AS ngày) AS ngày
TỪ người dùng),

- kết hợp chéo để nhận tất cả các kết hợp ngày người dùng có thể có

all_users_dates AS (
CHỌN DISTINCT
user_id,
d.dates
TỪ người dùng
CROSS JOIN date_vector d),

- bảng người dùng tham gia bên trái vào tất cả các kết hợp ngày của người dùng trên ID người dùng và ngày phù hợp (rỗng vào những ngày mà người dùng không tham gia), tham gia vào ngày đăng ký của mỗi người dùng này, loại trừ các kết hợp ngày của người dùng rơi vào trước khi người dùng đăng ký

t1 AS (
CHỌN
a.dates - c.join_date AS day_no,
b.user_id
TỪ all_users_dates a
TRÁI THAM GIA người dùng b
BẬT a.user_id = b.user_id
VÀ a.dates = b .action_date
THAM GIA join_dates c
ON a.user_id = c.user_id
WHERE a.dates - c.join_date & gt; = 0)

- nhóm theo ngày kể từ khi đăng ký, đếm (không rỗng) ID người dùng là người dùng đang hoạt động, tổng số người dùng và thương số là tỷ lệ giữ chân

SELECT
day_no,
COUNT (*) AS n_total,
COUNT (DISTINCT user_id) AS n_active,
ROUND (1,0 * COUNT (DISTINCT user_id) / COUNT (*), 2) AS re chú ý
TỪ t1
NHÓM THEO 1


Xem thêm những thông tin liên quan đến chủ đề bài tập thực hành sql cho người mới bắt đầu

SQL-C2108L: Buổi 5. avg, max, min, sum, top, order by, join, datediff, getdate, group by, left join

  • Tác giả: V1Study.com
  • Ngày đăng: 2022-03-18
  • Đánh giá: 4 ⭐ ( 2906 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: SQL-C2108L: Buổi 5.
    - avg, max, min, sum
    - top, top với percent, top với with ties
    - order by với asc, desc
    - join, left join, right join, full join
    - datediff, getdate
    - group by

Hướng dẫn học SQL cho người mới bắt đầu với SQL Server

  • Tác giả: openplanning.net
  • Đánh giá: 4 ⭐ ( 3573 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

bài tập truy vấn sql nâng cao (Bài tập SQL cho tester )

  • Tác giả: bytuong.com
  • Đánh giá: 3 ⭐ ( 6282 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

Gợi ý trang web và bài tập thực hành JavaScript cơ bản cho người mới

  • Tác giả: vn.got-it.ai
  • Đánh giá: 3 ⭐ ( 3325 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Bài viết gợi ý những bài tập thực hành JavaScript. Got It gợi ý cho bạn 3 trang web giải bài tập trực tuyến để luyện tập, nâng cao trình độ JavaScript trước khi bắt đầu giải quyết các dự án thực tế.

Học SQL: Hướng dẫn học SQL hoàn chỉnh cho người mới bắt đầu

  • Tác giả: vn.bitdegree.org
  • Đánh giá: 5 ⭐ ( 2493 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Hướng dẫn học SQL hoàn chỉnh: khám phá các cách học SQL tốt nhất từ hướng dẫn dạy cách học SQL dành cho người mới bắt đầu cũng như đã có kinh nghiệm

[MySQL Tutorial] Hướng dẫn SQL cho người mới bắt đầu

  • Tác giả: www.codelean.vn
  • Đánh giá: 4 ⭐ ( 6983 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: CodeLean.vn là nơi chia sẻ kiến thức của những người học, làm và dạy trong lĩnh vực công nghệ thông tin.

Hướng dẫn tự học SQL cho người mới bắt đầu?

  • Tác giả: stanford.com.vn
  • Đánh giá: 3 ⭐ ( 5673 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình

Xem Thêm  Thay đổi phông chữ CSS - thay đổi họ phông chữ trong css

By ads_php