Nat Taylor — Blog, AI, Product Management & Tinkering

Duplicative time periods in dbt

Published on .

WordPress database error: [<div style="clear:both">&nbsp;</div><div class="queries" style="clear:both; margin_bottom:2px; border: red dotted thin;">Queries made or created this session were<br/> <ol> <li>Raw query: SELECT * FROM wp_options WHERE </li> <li>Rewritten: SELECT * FROM wp_options WHERE </li> <li>With Placeholders: SELECT * FROM wp_options WHERE </li> <li>Prepare: SELECT * FROM wp_options WHERE </li> </ol> </div><div style="clear:both; margin_bottom:2px; border: red dotted thin;" class="error_message" style="border-bottom:dotted blue thin;">Error occurred at line 1644 in Function prepare_query. <br/> Error message was: Problem preparing the PDO SQL Statement. Error was: SQLSTATE[HY000]: General error: 1 incomplete input </div><pre>#0 /home/nattaylor/public_html/wordpress/wp-content/db.php(2746): WP_SQLite_DB\PDOEngine->get_error_message() #1 /home/nattaylor/public_html/wordpress/wp-content/db.php(3484): WP_SQLite_DB\wpsqlitedb->query('...') #2 /home/nattaylor/public_html/wordpress/wp-content/db.php(2952): WP_SQLite_DB\PDOSQLiteDriver->execute_duplicate_key_update() #3 /home/nattaylor/public_html/wordpress/wp-content/db.php(1893): WP_SQLite_DB\PDOSQLiteDriver->rewrite_query('...', '...') #4 /home/nattaylor/public_html/wordpress/wp-content/db.php(1357): WP_SQLite_DB\PDOEngine->execute_insert_query_new('...') #5 /home/nattaylor/public_html/wordpress/wp-content/db.php(2739): WP_SQLite_DB\PDOEngine->query('...') #6 /home/nattaylor/public_html/wordpress/wp-includes/option.php(1143): WP_SQLite_DB\wpsqlitedb->query('...') #7 /home/nattaylor/public_html/wordpress/wp-includes/option.php(1552): add_option('...', 1758176844, '', '...') #8 /home/nattaylor/public_html/wordpress/wp-content/plugins/syntax-highlighting-code-block/inc/functions.php(671): set_transient('...', Array, 2592000) #9 /home/nattaylor/public_html/wordpress/wp-includes/class-wp-block.php(586): Syntax_Highlighting_Code_Block\render_block(Array, '...', Object(WP_Block)) #10 /home/nattaylor/public_html/wordpress/wp-includes/blocks.php(2359): WP_Block->render() #11 /home/nattaylor/public_html/wordpress/wp-includes/blocks.php(2431): render_block(Array) #12 /home/nattaylor/public_html/wordpress/wp-includes/class-wp-hook.php(324): do_blocks('...') #13 /home/nattaylor/public_html/wordpress/wp-includes/plugin.php(205): WP_Hook->apply_filters('...', Array) #14 /home/nattaylor/public_html/wordpress/wp-includes/post-template.php(256): apply_filters('...', '...') #15 /home/nattaylor/public_html/wordpress/wp-content/themes/ntdc/index.php(70): the_content() #16 /home/nattaylor/public_html/wordpress/wp-includes/template-loader.php(106): include('...') #17 /home/nattaylor/public_html/wordpress/wp-blog-header.php(19): require_once('...') #18 /home/nattaylor/public_html/wordpress/index.php(17): require('...') </pre>]
SELECT * FROM wp_options WHERE

WordPress database error: [<div style="clear:both">&nbsp;</div><div class="queries" style="clear:both; margin_bottom:2px; border: red dotted thin;">Queries made or created this session were<br/> <ol> <li>Raw query: SELECT * FROM wp_options WHERE </li> <li>Rewritten: SELECT * FROM wp_options WHERE </li> <li>With Placeholders: SELECT * FROM wp_options WHERE </li> <li>Prepare: SELECT * FROM wp_options WHERE </li> </ol> </div><div style="clear:both; margin_bottom:2px; border: red dotted thin;" class="error_message" style="border-bottom:dotted blue thin;">Error occurred at line 1644 in Function prepare_query. <br/> Error message was: Problem preparing the PDO SQL Statement. Error was: SQLSTATE[HY000]: General error: 1 incomplete input </div><pre>#0 /home/nattaylor/public_html/wordpress/wp-content/db.php(2746): WP_SQLite_DB\PDOEngine->get_error_message() #1 /home/nattaylor/public_html/wordpress/wp-content/db.php(3484): WP_SQLite_DB\wpsqlitedb->query('...') #2 /home/nattaylor/public_html/wordpress/wp-content/db.php(2952): WP_SQLite_DB\PDOSQLiteDriver->execute_duplicate_key_update() #3 /home/nattaylor/public_html/wordpress/wp-content/db.php(1893): WP_SQLite_DB\PDOSQLiteDriver->rewrite_query('...', '...') #4 /home/nattaylor/public_html/wordpress/wp-content/db.php(1357): WP_SQLite_DB\PDOEngine->execute_insert_query_new('...') #5 /home/nattaylor/public_html/wordpress/wp-content/db.php(2739): WP_SQLite_DB\PDOEngine->query('...') #6 /home/nattaylor/public_html/wordpress/wp-includes/option.php(1143): WP_SQLite_DB\wpsqlitedb->query('...') #7 /home/nattaylor/public_html/wordpress/wp-includes/option.php(1554): add_option('...', Array, '', '...') #8 /home/nattaylor/public_html/wordpress/wp-content/plugins/syntax-highlighting-code-block/inc/functions.php(671): set_transient('...', Array, 2592000) #9 /home/nattaylor/public_html/wordpress/wp-includes/class-wp-block.php(586): Syntax_Highlighting_Code_Block\render_block(Array, '...', Object(WP_Block)) #10 /home/nattaylor/public_html/wordpress/wp-includes/blocks.php(2359): WP_Block->render() #11 /home/nattaylor/public_html/wordpress/wp-includes/blocks.php(2431): render_block(Array) #12 /home/nattaylor/public_html/wordpress/wp-includes/class-wp-hook.php(324): do_blocks('...') #13 /home/nattaylor/public_html/wordpress/wp-includes/plugin.php(205): WP_Hook->apply_filters('...', Array) #14 /home/nattaylor/public_html/wordpress/wp-includes/post-template.php(256): apply_filters('...', '...') #15 /home/nattaylor/public_html/wordpress/wp-content/themes/ntdc/index.php(70): the_content() #16 /home/nattaylor/public_html/wordpress/wp-includes/template-loader.php(106): include('...') #17 /home/nattaylor/public_html/wordpress/wp-blog-header.php(19): require_once('...') #18 /home/nattaylor/public_html/wordpress/index.php(17): require('...') </pre>]
SELECT * FROM wp_options WHERE

Imagine you’re implementing a dbt model that requires columns of counts of things that occurred in a trailing 1, 7, 14, …n day window. Worse yet you have to do this for 2 classes of things. You will need to implement basically the same SQL over 14 lines. Of course if you need to tweak the pattern, you have to edit in 14 places.

Jinja’s for-loops (docs) can save you. Below is an example of how to implement.

{% set periods = [1, 7, 14, 30, 45, 60, 90, 365] %}
SELECT
  id,
  <strong>MIN</strong>(datetime_created) AS first_thing,
  <strong>MAX</strong>(datetime_created) AS last_thing,
  CAST(<strong>count</strong>(*)/<strong>count</strong>(distinct date_trunc('month', datetime_created)) AS INT) avg_monthly_things,
  {% for n in periods %}
    <strong>COUNT</strong>(iff(DATEDIFF(days, owner_datetime_created, datetime_created)<={{n}}, 1, null)) things_first_{{n}}_days,
  {% endfor %}
  {% for n in periods %}
    <strong>COUNT</strong>(iff(DATEDIFF(days, thing.datetime_created, current_timestamp)<={{n}}, 1, null)) things_last_{{n}}_days,
  {% endfor %}
  <strong>COUNT</strong>(*) AS things
FROM thing
GROUP BY id

asd

Post Navigation

«
»