17. Referal tarmog'i: chuqurlik
QiyinCTE & RecursiveUzum
Ilovada foydalanuvchilar bir-birini taklif qiladi. users jadvalida
referred_by — kim taklif qilgani (NULL bo'lsa — o'zi kelgan).
Foydalanuvchi Aziz Karimov ning butun referal daraxtini toping:
u taklif qilganlar, ular taklif qilganlar va hokazo.
Qaytaring: name, depth — bu yerda Azizning bevosita takliflari uchun
depth = 1, ularning takliflari uchun depth = 2 va hokazo.
Azizning o'zi natijaga kirmaydi.
Saralash: depth o'sish, name o'sish.
Jadval: users
| Ustun | Turi |
|---|---|
| id | INT PK |
| name | VARCHAR |
| referred_by | INT NULL |
Namuna ma'lumotlar
sample
users
| idINT | nameVARCHAR(50) | referred_byINT |
|---|---|---|
| 1 | Aziz Karimov | NULL |
| 2 | Nilufar Rahimova | 1 |
| 3 | Bekzod Tursunov | 2 |
| 4 | Malika Yusupova | NULL |
Kutilgan natija
| name | depth |
|---|---|
| Nilufar Rahimova | 1 |
| Bekzod Tursunov | 2 |
sample-full
users
| idINT | nameVARCHAR(50) | referred_byINT |
|---|---|---|
| 1 | Aziz Karimov | NULL |
| 2 | Nilufar Rahimova | 1 |
| 3 | Bekzod Tursunov | 1 |
| 4 | Malika Yusupova | 2 |
| 5 | Sardor Nazarov | 2 |
| 6 | Dilnoza Ergasheva | 3 |
| 7 | Jasur Qodirov | 4 |
| 8 | Kamola Saidova | 4 |
| 9 | Otabek Mirzayev | 6 |
| 10 | Zilola Hamidova | NULL |
| 11 | Rustam Aliyev | 10 |
| 12 | Feruza Sobirova | 7 |
| 13 | Shohruh Ismoilov | 9 |
| 14 | Gulnora Tosheva | 11 |
Kutilgan natija
| name | depth |
|---|---|
| Bekzod Tursunov | 1 |
| Nilufar Rahimova | 1 |
| Dilnoza Ergasheva | 2 |
| Malika Yusupova | 2 |
| Sardor Nazarov | 2 |
| Jasur Qodirov | 3 |
| Kamola Saidova | 3 |
| Otabek Mirzayev | 3 |
| Feruza Sobirova | 4 |
| Shohruh Ismoilov | 4 |
sample-extra
users
| idINT | nameVARCHAR(50) | referred_byINT |
|---|---|---|
| 1 | Aziz Karimov | NULL |
| 2 | Anvar Ismoilov | 1 |
| 4 | Laylo Hasanova | 1 |
| 5 | Jasur Mirzayev | 4 |
Kutilgan natija
| name | depth |
|---|---|
| Anvar Ismoilov | 1 |
| Laylo Hasanova | 1 |
| Jasur Mirzayev | 2 |
O'xshash masalalar
Konsol
Natijani ko'rish uchun so'rovni ishga tushiring.