monthly-reports.txt

gistfile1.txt

monthlyUserSignins
  function main() {return join(Events({from_date: "<<fromDate>>",to_date: "<<toDate>>",event_selectors: [{event: "USER_SIGN_IN"}]}), People(), {type: "inner"}).map(function (result) {var date = new Date(result.event.time);var dd = (date.getDate() < 10 ? "0" : "") + date.getDate();var MM = ((date.getMonth() + 1) < 10 ? "0" : "") + (date.getMonth() + 1);var yyyy = date.getFullYear();return {"date": MM+"-"+dd+"-"+yyyy,"userId":result.user.properties["External ID"],"isDeepLinkSession":result.event.properties.isDeepLinkSession}}).groupBy(["userId"],mixpanel.reducer.count()).map(result => ({"userId": result.key[0],"count": result.value})).reduce(mixpanel.reducer.count())}
monthlyUserStarted
  function main() {return join(Events({from_date: "2019-11-01",to_date: "2019-11-30",event_selectors: [{event: "WATCH_NAVIGATION"},{event: "UNDERSTAND_NAVIGATION"},{event: "PRACTICE_NAVIGATION"}]}), People(), {type: "inner"}).map(result => ({"eventName": result.event.name,"skillId": result.event.properties["Skill ID"],"distinctId": result.user["distinct_id"]})).groupBy(["distinctId"],mixpanel.reducer.count()).reduce(mixpanel.reducer.count())}

skillAssignedAPP1040
  SELECT count(*) FROM (SELECT a.profile_id,((a.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='APP1040') AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillAssignedQTY2020
  SELECT count(*) FROM (SELECT a.profile_id,((a.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='QTY2020') AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillAssignedSFT1010
  SELECT count(*) FROM (SELECT a.profile_id,((a.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='SFT1010') AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillAssignedAPP1030
  SELECT count(*) FROM (SELECT a.profile_id,((a.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='APP1030') AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillAssignedAPP1010
  SELECT count(*) FROM (SELECT a.profile_id,((a.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='APP1010') AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillAssignedAPP1050
  SELECT count(*) FROM (SELECT a.profile_id,((a.created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='APP1050') AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
    
monthlyUserStartedAPP1040    
  function main() {return join(Events({from_date: "<<fromDate>>",to_date: "<<toDate>>",event_selectors: [{event: "WATCH_NAVIGATION"},{event: "UNDERSTAND_NAVIGATION"},{event: "PRACTICE_NAVIGATION"}]}), People(), {type: "inner"}).map(result => ({"eventName": result.event.name,"distinctId": result.user["distinct_id"],"currentUrl": result.event.properties["$current_url"],"isDeepLinkSession":result.event.properties.isDeepLinkSession})).filter((ev) => (ev.currentUrl.includes("APP1040"))).groupBy(["distinctId"],mixpanel.reducer.count()).reduce(mixpanel.reducer.count())}
monthlyUserStartedQTY2020  
  function main() {return join(Events({from_date: "<<fromDate>>",to_date: "<<toDate>>",event_selectors: [{event: "WATCH_NAVIGATION"},{event: "UNDERSTAND_NAVIGATION"},{event: "PRACTICE_NAVIGATION"}]}), People(), {type: "inner"}).map(result => ({"eventName": result.event.name,"distinctId": result.user["distinct_id"],"currentUrl": result.event.properties["$current_url"],"isDeepLinkSession":result.event.properties.isDeepLinkSession})).filter((ev) => (ev.currentUrl.includes("QTY2020"))).groupBy(["distinctId"],mixpanel.reducer.count()).reduce(mixpanel.reducer.count())}
monthlyUserStartedSFT1010  
  function main() {return join(Events({from_date: "<<fromDate>>",to_date: "<<toDate>>",event_selectors: [{event: "WATCH_NAVIGATION"},{event: "UNDERSTAND_NAVIGATION"},{event: "PRACTICE_NAVIGATION"}]}), People(), {type: "inner"}).map(result => ({"eventName": result.event.name,"distinctId": result.user["distinct_id"],"currentUrl": result.event.properties["$current_url"],"isDeepLinkSession":result.event.properties.isDeepLinkSession})).filter((ev) => (ev.currentUrl.includes("SFT1010"))).groupBy(["distinctId"],mixpanel.reducer.count()).reduce(mixpanel.reducer.count())}
monthlyUserStartedAPP1030
  function main() {return join(Events({from_date: "<<fromDate>>",to_date: "<<toDate>>",event_selectors: [{event: "WATCH_NAVIGATION"},{event: "UNDERSTAND_NAVIGATION"},{event: "PRACTICE_NAVIGATION"}]}), People(), {type: "inner"}).map(result => ({"eventName": result.event.name,"distinctId": result.user["distinct_id"],"currentUrl": result.event.properties["$current_url"],"isDeepLinkSession":result.event.properties.isDeepLinkSession})).filter((ev) => (ev.currentUrl.includes("APP1030"))).groupBy(["distinctId"],mixpanel.reducer.count()).reduce(mixpanel.reducer.count())}
monthlyUserStartedAPP1010
  function main() {return join(Events({from_date: "<<fromDate>>",to_date: "<<toDate>>",event_selectors: [{event: "WATCH_NAVIGATION"},{event: "UNDERSTAND_NAVIGATION"},{event: "PRACTICE_NAVIGATION"}]}), People(), {type: "inner"}).map(result => ({"eventName": result.event.name,"distinctId": result.user["distinct_id"],"currentUrl": result.event.properties["$current_url"],"isDeepLinkSession":result.event.properties.isDeepLinkSession})).filter((ev) => (ev.currentUrl.includes("APP1010"))).groupBy(["distinctId"],mixpanel.reducer.count()).reduce(mixpanel.reducer.count())}
monthlyUserStartedAPP1050
  function main() {return join(Events({from_date: "<<fromDate>>",to_date: "<<toDate>>",event_selectors: [{event: "WATCH_NAVIGATION"},{event: "UNDERSTAND_NAVIGATION"},{event: "PRACTICE_NAVIGATION"}]}), People(), {type: "inner"}).map(result => ({"eventName": result.event.name,"distinctId": result.user["distinct_id"],"currentUrl": result.event.properties["$current_url"],"isDeepLinkSession":result.event.properties.isDeepLinkSession})).filter((ev) => (ev.currentUrl.includes("APP1050"))).groupBy(["distinctId"],mixpanel.reducer.count()).reduce(mixpanel.reducer.count())}

skillCompletedAPP1040
  SELECT count(*) FROM (SELECT a.profile_id,((a.completed_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist,a.completed_at FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='APP1040' AND a.completed_at is NOT NULL) AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillCompletedQTY2020
  SELECT count(*) FROM (SELECT a.profile_id,((a.completed_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist,a.completed_at FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='QTY2020' AND a.completed_at is NOT NULL) AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillCompletedSFT1010
  SELECT count(*) FROM (SELECT a.profile_id,((a.completed_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist,a.completed_at FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='SFT1010' AND a.completed_at is NOT NULL) AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillCompletedAPP1030
  SELECT count(*) FROM (SELECT a.profile_id,((a.completed_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist,a.completed_at FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='APP1030' AND a.completed_at is NOT NULL) AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59'
skillCompletedAPP1010
  SELECT count(*) FROM (SELECT a.profile_id,((a.completed_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist,a.completed_at FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='APP1010' AND a.completed_at is NOT NULL) AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';
skillCompletedAPP1050
  SELECT count(*) FROM (SELECT a.profile_id,((a.completed_at AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Calcutta') AS ist,a.completed_at FROM bigspring.assignment a INNER JOIN bigspring.skill s ON a.skill_id=s.id  WHERE s.company_id='cf8300a4-2273-11e9-89de-4bf7650ce0e2' AND s.external_id='APP1050' AND a.completed_at is NOT NULL) AS TIME_table WHERE ist BETWEEN '<<fromDate>> 00:00:00' AND '<<toDate>> 23:59:59';