Changes between Version 1 and Version 2 of AdvancedReports
- Timestamp:
- 07/02/26 16:05:01 (4 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedReports
v1 v2 7 7 SET search_path TO trekr; 8 8 9 WITH params AS ( 10 SELECT 2026::int AS report_year 11 ), 12 months AS ( 9 WITH months AS ( 13 10 SELECT generate_series(1, 12) AS month_no 14 11 ), … … 35 32 LEFT JOIN incomes i 36 33 ON i.user_id = fb.user_id 37 AND EXTRACT(YEAR FROM i.date)::int = (SELECT report_year FROM params) 34 AND i.date >= DATE '2026-01-01' 35 AND i.date < DATE '2027-01-01' 38 36 AND EXTRACT(MONTH FROM i.date)::int = m.month_no 39 37 GROUP BY fb.user_id, m.month_no … … 117 115 118 116 FBM <- FB x M 119 IY <- sigma_{ YEAR(i.date)=Y}(incomes i)117 IY <- sigma_{i.date >= '2026-01-01' AND i.date < '2027-01-01'}(incomes i) 120 118 MI0 <- FBM leftouterjoin_{FBM.user_id = i.user_id AND FBM.month_no = MONTH(i.date)} IY 121 119 MI <- gamma_{user_id, month_no; … … 157 155 SET search_path TO trekr; 158 156 159 WITH params AS ( 160 SELECT 2026::int AS report_year 161 ), 162 months AS ( 157 WITH months AS ( 163 158 SELECT generate_series(1, 12) AS month_no 164 159 ), … … 187 182 LEFT JOIN training_sessions ts 188 183 ON ts.training_user_id = tb.user_id 189 AND EXTRACT(YEAR FROM ts.date)::int = (SELECT report_year FROM params) 184 AND ts.date >= DATE '2026-01-01' 185 AND ts.date < DATE '2027-01-01' 190 186 AND EXTRACT(MONTH FROM ts.date)::int = m.month_no 191 187 GROUP BY tb.user_id, m.month_no … … 276 272 277 273 TBM <- TB x M 278 TSY <- sigma_{ YEAR(ts.date)=Y}(training_sessions ts)274 TSY <- sigma_{ts.date >= '2026-01-01' AND ts.date < '2027-01-01'}(training_sessions ts) 279 275 MS0 <- TBM leftouterjoin_{TBM.user_id = ts.training_user_id AND TBM.month_no = MONTH(ts.date)} TSY 280 276 MS <- gamma_{user_id, month_no; … … 330 326 SET search_path TO trekr; 331 327 332 WITH params AS ( 333 SELECT 2026::int AS report_year 334 ), 335 discipline_base AS ( 328 WITH discipline_base AS ( 336 329 SELECT 337 330 du.user_id, … … 362 355 CASE WHEN COALESCE(dc.procent, 0) >= 80 THEN 1 ELSE 0 END AS strong_day 363 356 FROM daily_completion dc 364 WHERE EXTRACT(YEAR FROM dc.date)::int = (SELECT report_year FROM params)357 WHERE EXTRACT(YEAR FROM dc.date)::int = 2026 365 358 ), 366 359 daily_completion_stats AS ( … … 402 395 LEFT JOIN task_daily_completion tdc 403 396 ON tdc.daily_completion_id = dc.daily_completion_id 404 WHERE EXTRACT(YEAR FROM dc.date)::int = (SELECT report_year FROM params)397 WHERE EXTRACT(YEAR FROM dc.date)::int = 2026 405 398 GROUP BY dc.user_id 406 399 ) … … 464 457 COUNT_DISTINCT(COALESCE(t.custom_tracking_id,'core'))->task_category_span}(TM1) 465 458 466 ADC0 <- sigma_{YEAR(dc.date)= Y}(daily_completion dc)459 ADC0 <- sigma_{YEAR(dc.date)=2026}(daily_completion dc) 467 460 ADC <- alpha_{COALESCE(dc.procent,0)->procent, 468 461 CASE(procent>=80,1,0)->strong_day}(ADC0) … … 516 509 SET search_path TO trekr; 517 510 518 WITH params AS ( 519 SELECT 2026::int AS report_year 520 ), 521 months AS ( 511 WITH months AS ( 522 512 SELECT generate_series(1, 12) AS month_no 523 513 ), … … 539 529 a.buy_date 540 530 FROM assets a 541 WHERE EXTRACT(YEAR FROM a.buy_date)::int = (SELECT report_year FROM params) 531 WHERE a.buy_date >= DATE '2026-01-01' 532 AND a.buy_date < DATE '2027-01-01' 542 533 ), 543 534 ticker_rollup AS ( … … 594 585 LEFT JOIN assets a 595 586 ON a.user_id = ib.user_id 596 AND EXTRACT(YEAR FROM a.buy_date)::int = (SELECT report_year FROM params) 587 AND a.buy_date >= DATE '2026-01-01' 588 AND a.buy_date < DATE '2027-01-01' 597 589 AND EXTRACT(MONTH FROM a.buy_date)::int = m.month_no 598 590 GROUP BY ib.user_id, m.month_no … … 646 638 COALESCE(a.quantity,0)*COALESCE(a.buy_price,0)->invested_amount, 647 639 a.buy_date} 648 (sigma_{ YEAR(a.buy_date)=Y}(assets a))640 (sigma_{a.buy_date >= '2026-01-01' AND a.buy_date < '2027-01-01'}(assets a)) 649 641 650 642 TR <- gamma_{user_id, ticker_symbol; … … 671 663 672 664 IBM <- IB x M 673 AY <- sigma_{ YEAR(a.buy_date)=Y}(assets a)665 AY <- sigma_{a.buy_date >= '2026-01-01' AND a.buy_date < '2027-01-01'}(assets a) 674 666 MI0 <- IBM leftouterjoin_{IBM.user_id=a.user_id AND IBM.month_no=MONTH(a.buy_date)} AY 675 667 MI <- gamma_{user_id, month_no;
