{"id":6502,"date":"2019-03-12T09:31:53","date_gmt":"2019-03-12T16:31:53","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=6502"},"modified":"2025-06-13T17:22:10","modified_gmt":"2025-06-14T00:22:10","slug":"get-a-bigger-picture-with-n1ql-window-functions-and-cte","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/get-a-bigger-picture-with-n1ql-window-functions-and-cte\/","title":{"rendered":"Get a Bigger Picture with N1QL Window Functions and CTE"},"content":{"rendered":"

Whether it is a personal need to understand your spending pattern from looking at the bank transactions, or seeking to improve the sales performance in your organization by looking at the sales activities, the real insights are only visible with additional aggregation and summarization of the transactional level data. Traditional RDBMS provides this capability through the expressive power of the SQL aggregation. Window functions was added to the ANSI SQL standard to further extend the ability to express more complex questions with the SQL construct.<\/p>\n

Couchbase N1QL now supports the window functions and common table expression (CTE) in its 6.5 release. Developers can extend their applications to meet more complex business use cases, as well as empowering data analysts to get the answers they need without performing additional post processing in Excel.<\/p>\n

For this article I will focus on couple of examples on how you can leverage N1QL Window functions and CTE to address two very common business questions.<\/p>\n

Sales activities duration by customers<\/h3>\n

In this first example for a sales activity management system, we want to provide a report to show the amount of time that the sales team has spent working with their customers for\u00a0 January-2019. I will break the query down into two steps:<\/p>\n

1) Get a list of meetings and the meeting duration that the sales team has conducted with their customers. The total time spent for all customers ‘total_time_spent’ is calculated by summing the duration of the appointments with an empty OVER () clause, which will perform the summing for the entire result set. The total time spent by customer\u00a0 ‘account_time_spent’ uses the same construct, but with the ‘accid’ for the PARTITION clause.<\/p>\n

SELECT  c.name, a.startDate, a.title, a.duration, \r\n        SUM(a.duration) OVER() as total_time_spent,\r\n        SUM(a.duration) OVER(PARTITION BY a.accid) AS account_time_spent\r\nFROM crm a\r\n  INNER JOIN crm c ON a.accid = c.id AND  c.type='account'\r\nWHERE a.type='activity'\r\nAND a.activityType='Appointment'\r\nAND a.startDate BETWEEN '2018-01-01' and '2018-01-31'\r\n<\/pre>\n
account_time_spent\tduration\tname\tstartDate\ttitle\ttotal_time_spent\r\n30\t\"30\"\t\"Smith, Avila and Cisneros\"\t\"2018-01-24 15:00:00\"\t\"Switchable coherent adapter\"\t25770\r\n510\t\"150\"\t\"Riddle Ltd\"\t\"2018-01-05 15:30:00\"\t\"Streamlined intermediate intranet\"\t25770\r\n510\t\"90\"\t\"Riddle Ltd\"\t\"2018-01-28 14:30:00\"\t\"Advanced solution-oriented synergy\"\t25770\r\n510\t\"90\"\t\"Riddle Ltd\"\t\"2018-01-01 15:00:00\"\t\"Ameliorated object-oriented methodology\"\t25770\r\n510\t\"30\"\t\"Riddle Ltd\"\t\"2018-01-10 08:00:00\"\t\"Object-based multi-state firmware\"\t25770\r\n510\t\"150\"\t\"Riddle Ltd\"\t\"2018-01-23 09:00:00\"\t\"Multi-layered systematic software\"\t25770\r\n120\t\"30\"\t\"Foster Inc\"\t\"2018-01-29 09:30:00\"\t\"Public-key bottom-line database\"\t25770\r\n120\t\"30\"\t\"Foster Inc\"\t\"2018-01-09 13:00:00\"\t\"Quality-focused local emulation\"\t25770\r\n120\t\"60\"\t\"Foster Inc\"\t\"2018-01-02 08:00:00\"\t\"Digitized motivating matrix\"\t25770\r\n120\t\"30\"\t\"Williams Ltd\"\t\"2018-01-22 08:30:00\"\t\"Versatile heuristic workforce\"\t25770\r\n120\t\"30\"\t\"Williams Ltd\"\t\"2018-01-24 13:30:00\"\t\"Front-line 4thgeneration help-desk\"\t25770\r\n120\t\"60\"\t\"Williams Ltd\"\t\"2018-01-14 14:30:00\"\t\"Visionary upward-trending success\"\t25770\r\n330\t\"150\"\t\"Reid Ltd\"\t\"2018-01-21 10:30:00\"\t\"Profound logistical archive\"\t25770\r\n330\t\"30\"\t\"Reid Ltd\"\t\"2018-01-13 13:30:00\"\t\"Down-sized coherent access\"\t25770\r\n330\t\"120\"\t\"Reid Ltd\"\t\"2018-01-02 12:00:00\"\t\"Front-line object-oriented moderator\"\t25770\r\n330\t\"30\"\t\"Reid Ltd\"\t\"2018-01-12 09:30:00\"\t\"Programmable reciprocal infrastructure\"\t25770\r\n....<\/pre>\n
2) Then use the two metrics to derive the percentage of the overall time the team spent with each customer.<\/p>\n
SELECT  c.name,  \r\n  ROUND(( SUM(SUM(a.duration)) OVER(PARTITION BY a.accid)  \/ \r\n   SUM(SUM(a.duration)) OVER()),2) as pct_time\r\nFROM crm a\r\nINNER JOIN crm c ON a.accid = c.id AND  c.type='account'\r\nWHERE a.type='activity'\r\n  AND a.activityType='Appointment'\r\n  AND a.startDate BETWEEN '2018-01-01' and '2018-01-31'\r\nGROUP BY c.name, a.accid\r\nORDER BY 2 DESC<\/pre>\n
name\tpct_time\r\n\"Johnson, Adams and Kelly\"\t0.17\r\n\"Davis Group\"\t0.08\r\n\"Gilbert-Morris\"\t0.08\r\n\"Torres and Sons\"\t0.07\r\n\"Reid Ltd\"\t0.07\r\n\"Medina-Daniels\"\t0.07\r\n\"Riddle Ltd\"\t0.05\r\n\"Henderson and Sons\"\t0.05\r\n\"Gill and Sons\"\t0.05\r\n\"Garcia-Young\"\t0.05\r\n\"Sullivan PLC\"\t0.03\r\n\"Brown-Rogers\"\t0.03\r\n\"Foster Inc\"\t0.03\r\n\"Wheeler Inc\"\t0.03\r\n\"Jarvis-Small\"\t0.03\r\n\"Jones-Fox\"\t0.03\r\n\"Lloyd, Blair and Pruitt\"\t0.03\r\n\"Vaughn LLC\"\t0.02\r\n<\/pre>\n
Sales Activities Month-over-Month<\/h3>\n
In this second example, the query shows how the number of sales related tasks have changed month over month for the year 2018.\u00a0 The query leverages N1QL CTE feature to improve readability of the query, an also the LAG window function to obtain the\u00a0 previous period task count.<\/p>\n
WITH current_period_task AS (\r\n  SELECT DATE_TRUNC_STR(a.startDate,'month') AS month,\r\n         COUNT(1) AS current_period_task_count\r\n  FROM crm a \r\n  WHERE a.type='activity' AND a.activityType = 'Task'\r\n    AND   DATE_PART_STR(a.startDate,'year') = 2018\r\n    GROUP BY DATE_TRUNC_STR(a.startDate,'month')\r\n), \r\nlast_period_task AS ( \r\n   SELECT x.month, x.current_period_task_count, \r\n          LAG(x.current_period_task_count) OVER ( ORDER BY x.month) AS last_period_task_count \r\n   FROM current_period_task x\r\n)\r\nSELECT b.month, \r\n       b.current_period_task_count, \r\n       ROUND(((b.current_period_task_count - b.last_period_task_count ) \/ b.last_period_task_count),2) AS MoMChg\r\nFROM last_period_task AS b<\/pre>\n
    \n
  1. The first CTE – ‘current_period_task’ defines the query to retrieve a count for all activity of type Task group by the calendar month.<\/li>\n
  2. The second CTE – ‘last_period_task’ reads from the first CTE, and also leverage the window function LAG to return the task_count for the previous month. Note that the ORDER BY clause is critical for the LAG function to work.<\/li>\n
  3. The main query reads from the second CTE – ‘last_period_task’ and derive the month over month calculation.<\/li>\n<\/ol>\n
    MoMChg\tcurrent_period_task_count\tmonth\r\n\t283\t\"2018-01-01\"\r\n-0.08\t260\t\"2018-02-01\"\r\n0.1\t287\t\"2018-03-01\"\r\n-0.08\t264\t\"2018-04-01\"\r\n0.11\t292\t\"2018-05-01\"\r\n0\t293\t\"2018-06-01\"\r\n-0.03\t285\t\"2018-07-01\"\r\n0\t285\t\"2018-08-01\"\r\n0\t284\t\"2018-09-01\"\r\n0\t283\t\"2018-10-01\"\r\n-0.05\t268\t\"2018-11-01\"\r\n0.06\t285\t\"2018-12-01\"\r\n<\/pre>\n
     <\/p>\n
    Resources<\/h3>\n