CRM overview

SELECT NVL(BUILTIN.DF(t.entitystatus), '(no stage)') AS stage,
       COUNT(*) AS opportunities,
       SUM(t.projectedtotal * t.exchangerate) AS projected,
       SUM(t.weightedtotal * t.exchangerate) AS weighted,
       AVG(t.probability) AS avg_probability,
       TO_CHAR(MIN(t.expectedclosedate), 'YYYY-MM-DD') AS next_close
  FROM transaction t
 WHERE t.type = 'Opprtnty'
   AND t.status IN ('Opprtnty:A', 'Opprtnty:B', 'A', 'B')
 GROUP BY NVL(BUILTIN.DF(t.entitystatus), '(no stage)')
 ORDER BY avg_probability, stage

SELECT t.id AS opportunity__id,
       t.tranid AS opportunity,
       t.title AS title,
       NVL(e.altname, e.entityid) AS customer,
       NVL(BUILTIN.DF(t.entitystatus), '(no stage)') AS stage,
       BUILTIN.DF(t.employee) AS sales_rep,
       TO_CHAR(t.expectedclosedate, 'YYYY-MM-DD') AS expected_close,
       TRUNC(SYSDATE) - TRUNC(t.trandate) AS days_open,
       t.probability AS probability,
       t.projectedtotal * t.exchangerate AS projected,
       t.weightedtotal * t.exchangerate AS weighted
  FROM transaction t
  JOIN entity e ON e.id = t.entity
 WHERE t.type = 'Opprtnty'
   AND t.status IN ('Opprtnty:A', 'Opprtnty:B', 'A', 'B')
 ORDER BY t.expectedclosedate

SELECT period, new_customers, customers_to_date
  FROM (SELECT m.period,
               m.new_customers,
               SUM(m.new_customers) OVER (ORDER BY m.period) AS customers_to_date
          FROM (SELECT TO_CHAR(c.firstsaledate, 'YYYY-MM') AS period,
                       COUNT(*) AS new_customers
                  FROM customer c
                 WHERE c.firstsaledate IS NOT NULL
                   AND c.parent IS NULL
                 GROUP BY TO_CHAR(c.firstsaledate, 'YYYY-MM')) m)
 WHERE period >= TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23), 'YYYY-MM')
 ORDER BY period

SELECT x.status,
       x.priority,
       SUM(CASE WHEN x.age <= 2 THEN 1 ELSE 0 END) AS age_0_2,
       SUM(CASE WHEN x.age BETWEEN 3 AND 7 THEN 1 ELSE 0 END) AS age_3_7,
       SUM(CASE WHEN x.age BETWEEN 8 AND 30 THEN 1 ELSE 0 END) AS age_8_30,
       SUM(CASE WHEN x.age > 30 THEN 1 ELSE 0 END) AS age_over_30,
       COUNT(*) AS open_cases
  FROM (SELECT NVL(BUILTIN.DF(sc.status), '(none)') AS status,
               NVL(BUILTIN.DF(sc.priority), '(none)') AS priority,
               TRUNC(SYSDATE) - TRUNC(sc.startdate) AS age
          FROM supportcase sc
          JOIN supportcasestatus s ON s.id = sc.status
         WHERE s.stage <> 'CLOSED'
           AND sc.isinactive = 'F') x
 GROUP BY x.status, x.priority
 ORDER BY open_cases DESC

SELECT sc.id AS case__id,
       sc.casenumber AS case_number,
       sc.title AS subject,
       BUILTIN.DF(sc.company) AS company,
       NVL(BUILTIN.DF(sc.status), '(none)') AS status,
       NVL(BUILTIN.DF(sc.priority), '(none)') AS priority,
       BUILTIN.DF(sc.assigned) AS assigned_to,
       BUILTIN.DF(sc.origin) AS origin,
       TO_CHAR(sc.startdate, 'YYYY-MM-DD') AS start_date,
       TRUNC(SYSDATE) - TRUNC(sc.startdate) AS days_open,
       TO_CHAR(sc.lastmodifieddate, 'YYYY-MM-DD') AS last_modified
  FROM supportcase sc
  JOIN supportcasestatus s ON s.id = sc.status
 WHERE s.stage <> 'CLOSED'
   AND sc.isinactive = 'F'
 ORDER BY sc.startdate

SELECT TO_CHAR(NVL(t.closedate, t.expectedclosedate), 'YYYY-MM') AS period,
       SUM(CASE WHEN t.status IN ('Opprtnty:C', 'C') THEN 1 ELSE 0 END) AS won,
       SUM(CASE WHEN t.status IN ('Opprtnty:D', 'D') THEN 1 ELSE 0 END) AS lost,
       COUNT(*) AS closed,
       SUM(CASE WHEN t.status IN ('Opprtnty:C', 'C') THEN t.projectedtotal * t.exchangerate ELSE 0 END) AS won_value,
       SUM(CASE WHEN t.status IN ('Opprtnty:D', 'D') THEN t.projectedtotal * t.exchangerate ELSE 0 END) AS lost_value,
       SUM(CASE WHEN t.status IN ('Opprtnty:C', 'C') THEN 1 ELSE 0 END) / COUNT(*) AS win_rate
  FROM transaction t
 WHERE t.type = 'Opprtnty'
   AND t.status IN ('Opprtnty:C', 'Opprtnty:D', 'C', 'D')
   AND NVL(t.closedate, t.expectedclosedate) >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 GROUP BY TO_CHAR(NVL(t.closedate, t.expectedclosedate), 'YYYY-MM')
 ORDER BY period