Best Practices for NQL

Crafting efficient and understandable NQL queries is crucial for leveraging the Narrative Data Collaboration Platform effectively. These combined best practices ensure clarity, reduce ambiguity, and maintain a standard query structure.

1. Query Structure and Naming Conventions

When writing NQL queries, adhering to structured naming conventions and clear query design is essential. This includes:

• Quoting Conventions: Always use double quotes (") around table names, dataset field names, attribute names, and attribute property names to adhere to NQL syntax rules and avoid potential conflicts with reserved keywords or special characters.
• Fully Qualified Names: Refer to all tables by their fully qualified names, including the full namespaces, in the FROM clause to avoid ambiguity. For example, "narrative"."rosetta_stone" for Rosetta Stone datasets.
• Table and Field Names Only in SELECT Statements: In the SELECT statement, avoid using the fully qualified name including the namespace (e.g. "company_data" or "rosetta_stone".) You can refer directly to the table name or attribute/field names. For queries involving multiple tables, alias the tables and use these aliases to refer to the fields in the SELECT statement.
• Aliasing for Clarity: Alias table names rather than using their fully qualified names in the SELECT statement. This practice enhances readability and helps to clearly distinguish between different tables and fields, especially in complex queries involving JOIN operations.
• No Ambiguity: Ensure there is no ambiguity when referring to fields, especially in queries involving multiple tables or JOINs. This includes using aliases effectively and choosing clear, descriptive names for these aliases.

Example Query

The following example query illustrates these combined best practices in action, querying Rosetta Stone datasets for user engagement metrics while adhering to the structured query design:

SELECT 
  rs."unique_id" AS "User ID", 
  COUNT(rs."event_id") AS "Total Events", 
  approx_count_distinct(sd."session_id") AS "Unique Sessions"
FROM 
  "narrative"."rosetta_stone" AS rs
JOIN 
  "company_data"."user_sessions" AS sd ON rs."unique_id" = sd."user_id"
WHERE 
  rs."event_timestamp" > CURRENT_TIMESTAMP - INTERVAL '7' DAYS
  AND sd."session_duration" > INTERVAL '5' MINUTE
GROUP BY 
  rs."unique_id"
ORDER BY 
  "Total Events" DESC
LIMIT 100;

In this query:

• We use fully qualified names for tables in the FROM and JOIN clauses, enhancing clarity and specificity.
• The SELECT statement avoids namespace prefixes, directly referencing field names and employing aliases (rs for Rosetta Stone, sd for user sessions data).
• Table and field aliases are used to simplify the SELECT, WHERE, and JOIN clauses, making the query easier to read and understand.
• Double quotes are used around all field and alias names, adhering to NQL syntax rules.

By following these best practices, NQL queries become more structured, understandable, and efficient, facilitating clearer analysis and decision-making based on the data retrieved.