Created
March 14, 2019 20:16
-
-
Save radiosterne/0b8ac195d2a5d52472e14c4250d36b09 to your computer and use it in GitHub Desktop.
Выгрузка лидов по филиалу и направлению без SEO и оффлайна
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select CAST (CreatedAt as DATE) as date, | |
| count(distinct Lead.StudentId) | |
| from CourseTimeline.Lead | |
| inner join CourseTimeline.LeadCampaign on Lead.CampaignId = LeadCampaign.Id | |
| inner join CourseTimeline.StudentStage on StudentStage.StudentId = Lead.StudentId | |
| where LeadPlatform != 4 | |
| and LeadPlatform != 0 | |
| and LeadCampaign.BranchId = 'F9A8B39B-BBB5-435C-8352-F4786271F3B9' | |
| and (StudentStage.TelemarketingStage != 105 or | |
| StudentStage.SellingStageChangedAt > StudentStage.TelemarketingStageChangedAt or | |
| exists(select * | |
| from CourseTimeline.EventRecord er | |
| inner join CourseTimeline.Event ev on er.EventId = ev.Id | |
| inner join CourseTimeline.EventType et on ev.EventTypeId = et.Id | |
| inner join CourseTimeline.EventCategory ec on et.CategoryId = ec.Id | |
| where er.StudentId = Lead.StudentId | |
| and ec.IsPromo = 1 | |
| and er.RecordStatus = 1)) | |
| and (Lead.CreatedAt > '2019-03-10 00:00:00.000' and Lead.CreatedAt < '2019-03-12 00:00:00.000') | |
| and LeadCampaign.Name like N'%Фортепиано%' | |
| group by CAST (CreatedAt as DATE) | |
| order by CAST (CreatedAt as DATE) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment