#5hard

5-Dars: CTE (WITH) va Murakkab Tahliliy So'rovlar

Common Table Expressions (WITH), kohort tahlili (Cohort Retention) va mijozlarning qaytish dinamikasi.

—(hali baholanmagan)

0marta ko‘rilgan

Yangi kurs

CTE (Common Table Expressions) va Kohort Tahlili

Katta va o'qilishi qiyin ichma-ich so'rovlarni tartibli va modulli qilish uchun WITH sintaksisi ishlatiladi.


code.sql
SQL
01WITH MonthlySignups AS (
02  SELECT id, DATE_TRUNC('month', created_at) AS signup_month
03  FROM users
04),
05UserFirstOrders AS (
06  SELECT user_id, MIN(created_at) AS first_order_date
07  FROM orders
08  GROUP BY user_id
09)
10SELECT 
11  s.signup_month,
12  COUNT(s.id) AS total_users,
13  COUNT(o.user_id) AS converted_users,
14  ROUND(COUNT(o.user_id)::numeric / COUNT(s.id) * 100, 1) AS conversion_rate
15FROM MonthlySignups s
16LEFT JOIN UserFirstOrders o ON s.id = o.user_id
17GROUP BY s.signup_month
18ORDER BY s.signup_month DESC;

Bilimingizni mustahkamlang

10 ta savol

Test Sinovi

Mavzu bo‘yicha tushunchalaringizni interaktiv test orqali sinovdan o‘tkazing va natijalarni bilib oling.

0 ta mashq

Amaliy Masalalar

Darslikda o‘rganilgan qoidalarga asosan real kod yozing, testlardan o‘tkazing va yechimlarni mustahkamlang.

Hozircha amaliy mashqlar ulanmagan

Fikr va baholash

Ushbu darslik sizga yoqdimi?

Dars sifati va mazmunini yaxshilash uchun o‘z haqqoniy bahoingizni qoldiring.

Baholash uchun kiring

Tegishli Manbalar va Amaliyot

Mavzuni to‘liq o‘zlashtirish uchun test sinovi, amaliy kodlash va bog‘liq darslar

Shu mavzuni sinab ko‘ring

SQL & Data Analitika Test Sinovi

O‘z bilimingizni interaktiv test savollari orqali tekshiring va darhol xatolarni tahlil qiling.

Testni boshlash

Amaliyot qiling

SQL & Data Analitika Amaliy Masalalari

Nazariyani real kod yozish bilan mustahkamlang, avtomatik testlardan o‘ting va XP to‘plang.

Masalalarni ko‘rish

Boshqa SQL & Data Analitika darslari

Kurs bo‘yicha keyingi va bog‘liq mavzular:

CTE (Common Table Expressions) va Kohort Tahlili

Katta va o'qilishi qiyin ichma-ich so'rovlarni tartibli va modulli qilish uchun WITH sintaksisi ishlatiladi.

code.sql

SQL

WITH MonthlySignups AS (

SELECT id, DATE_TRUNC('month', created_at) AS signup_month

FROM users

UserFirstOrders AS (

SELECT user_id, MIN(created_at) AS first_order_date

FROM orders

GROUP BY user_id

)

SELECT

s.signup_month,

COUNT(s.id) AS total_users,

COUNT(o.user_id) AS converted_users,

ROUND(COUNT(o.user_id)::numeric / COUNT(s.id) * 100, 1) AS conversion_rate

FROM MonthlySignups s

LEFT JOIN UserFirstOrders o ON s.id = o.user_id

GROUP BY s.signup_month

ORDER BY s.signup_month DESC;