83. Rekursiv: noldan pastga tushmaydigan qoldiq
QiyinCTE & RecursiveKapitalbank
Hisobdagi operatsiyalar seq tartibida bajariladi. Qoldiq har operatsiyadan
keyin yangilanadi, lekin hech qachon noldan past bo'lmaydi: agar yechish
qoldiqdan katta bo'lsa, qoldiq 0 ga tushadi.
Har bir operatsiyadan keyingi qoldiqni hisoblang.
Ustunlar: seq, amount, balance.
⚠️ Bu masalani oddiy SUM(...) OVER (...) bilan yechib bo'lmaydi —
har qadamdagi cheklov oldingi natijaga bog'liq.
Saralash: seq o'sish.
Jadval: tx
| Ustun | Turi |
|---|---|
| seq | INT PK |
| amount | NUMERIC(12,2) |
Namuna ma'lumotlar
sample
tx
| seqINT | amountNUMERIC(12,2) |
|---|---|
| 1 | 100 |
| 2 | -150 |
| 3 | 80 |
Kutilgan natija
| seq | amount | balance |
|---|---|---|
| 1 | 100 | 100 |
| 2 | -150 | 0 |
| 3 | 80 | 80 |
sample-full
tx
| seqINT | amountNUMERIC(12,2) |
|---|---|
| 1 | 500000 |
| 2 | -200000 |
| 3 | -450000 |
| 4 | 300000 |
| 5 | -100000 |
| 6 | -350000 |
| 7 | 700000 |
| 8 | -250000 |
| 9 | -600000 |
| 10 | 150000 |
| 11 | -50000 |
| 12 | 400000 |
Kutilgan natija
| seq | amount | balance |
|---|---|---|
| 1 | 500000 | 500000 |
| 2 | -200000 | 300000 |
| 3 | -450000 | 0 |
| 4 | 300000 | 300000 |
| 5 | -100000 | 200000 |
| 6 | -350000 | 0 |
| 7 | 700000 | 700000 |
| 8 | -250000 | 450000 |
| 9 | -600000 | 0 |
| 10 | 150000 | 150000 |
| 11 | -50000 | 100000 |
| 12 | 400000 | 500000 |
sample-extra
tx
| seqINT | amountNUMERIC(12,2) |
|---|---|
| 1 | -200000 |
| 2 | 488981.41 |
| 3 | -756798.7 |
| 4 | -599595.42 |
| 5 | -289559.96 |
| 6 | 150000 |
Kutilgan natija
| seq | amount | balance |
|---|---|---|
| 1 | -200000 | 0 |
| 2 | 488981.41 | 488981.41 |
| 3 | -756798.7 | 0 |
| 4 | -599595.42 | 0 |
| 5 | -289559.96 | 0 |
| 6 | 150000 | 150000 |
O'xshash masalalar
Konsol
Natijani ko'rish uchun so'rovni ishga tushiring.