Get a Bigger Picture with N1QL Window Functions and CTE

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.

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.

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.

Sales activities duration by customers

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  January-2019. I will break the query down into two steps:

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  ‘account_time_spent’ uses the same construct, but with the ‘accid’ for the PARTITION clause.

2) Then use the two metrics to derive the percentage of the overall time the team spent with each customer.

Sales Activities Month-over-Month

In this second example, the query shows how the number of sales related tasks have changed month over month for the year 2018.  The query leverages N1QL CTE feature to improve readability of the query, an also the LAG window function to obtain the  previous period task count.

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.
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.
3. The main query reads from the second CTE – ‘last_period_task’ and derive the month over month calculation.

Resources

• Download: Download Couchbase Server 6.5
• Documentation: Couchbase Server 6.5 What’s New
• All 6.5 Blogs

We would love to hear from you on how you liked the 6.5 features and how it’ll benefit your business going forward. Please share your feedback via the comments or in the forum.