SQL query examples

<!doctype html><html lang="en"><head> <meta charset="utf-8"> <title>SQL query examples</title> <link rel="shortcut icon" href="https://cratedb.com/hubfs/cratedb-favicon.png"> <meta name="description" content="CrateDB uses SQL for data query, offering great simplicity and flexibility to developers."> <style> .cr-header-full-height { height: 100vh; } </style>   <script src="https://cratedb.com/hs-fs/hub/19927462/hub_generated/template_assets/103134656537/1676853350416/sf2-crate/js/jquery-3.5.1.min.js"></script> <meta name="viewport" content="width=device-width, initial-scale=1"> <meta property="og:description" content="CrateDB uses SQL for data query, offering great simplicity and flexibility to developers."> <meta property="og:title" content="SQL query examples"> <meta name="twitter:description" content="CrateDB uses SQL for data query, offering great simplicity and flexibility to developers."> <meta name="twitter:title" content="SQL query examples"> <style> a.cta_button{-moz-box-sizing:content-box !important;-webkit-box-sizing:content-box !important;box-sizing:content-box !important;vertical-align:middle}.hs-breadcrumb-menu{list-style-type:none;margin:0px 0px 0px 0px;padding:0px 0px 0px 0px}.hs-breadcrumb-menu-item{float:left;padding:10px 0px 10px 10px}.hs-breadcrumb-menu-divider:before{content:'›';padding-left:10px}.hs-featured-image-link{border:0}.hs-featured-image{float:right;margin:0 0 20px 20px;max-width:50%}@media (max-width: 568px){.hs-featured-image{float:none;margin:0;width:100%;max-width:100%}}.hs-screen-reader-text{clip:rect(1px, 1px, 1px, 1px);height:1px;overflow:hidden;position:absolute !important;width:1px} </style> <link rel="stylesheet" href="https://cratedb.com/hs-fs/hub/19927462/hub_generated/template_assets/52693799430/1721897980656/sf2-crate/css/main.min.css"> <link rel="stylesheet" href="https://cratedb.com/hs-fs/hub/19927462/hub_generated/template_assets/52694827330/1729004392151/sf2-crate/css/theme-overrides.min.css"> <link rel="stylesheet" href="https://cratedb.com/hs-fs/hub/19927462/hub_generated/module_assets/55523967869/1722426745670/module_55523967869_Header_Notif_Bar.min.css"> <link rel="stylesheet" href="https://cratedb.com/hs-fs/hub/19927462/hub_generated/module_assets/52694764069/1701097527939/module_52694764069_menu-section.min.css"> <link rel="stylesheet" href="https://cratedb.com/hs-fs/hub/19927462/hub_generated/module_assets/53642957898/1710775451648/module_53642957898_code-tabs.min.css">  <style id="hs_editor_style" type="text/css"> .dnd_area-row-0-force-full-width-section > .row-fluid { max-width: none !important; } .dnd_area-row-1-max-width-section-centering > .row-fluid { max-width: 1240px !important; margin-left: auto !important; margin-right: auto !important; } /* HubSpot Styles (default) */ .dnd_area-row-0-background-layers { background-image: url('https://cratedb.com/hubfs/cr-header-mesh.jpg') !important; background-position: center center !important; background-size: cover !important; background-repeat: no-repeat !important; } .dnd_area-row-1-background-layers { background-image: linear-gradient(rgba(243, 243, 243, 1), rgba(243, 243, 243, 1)) !important; background-position: left top !important; background-size: auto !important; background-repeat: no-repeat !important; } .dnd_area-row-3-background-layers { background-image: linear-gradient(rgba(243, 243, 243, 1), rgba(243, 243, 243, 1)) !important; background-position: left top !important; background-size: auto !important; background-repeat: no-repeat !important; } .dnd_area-row-4-padding { padding-top: 0px !important; } .dnd_area-row-6-background-layers { background-image: linear-gradient(rgba(243, 243, 243, 1), rgba(243, 243, 243, 1)) !important; background-position: left top !important; background-size: auto !important; background-repeat: no-repeat !important; } .dnd_area-row-8-background-layers { background-image: linear-gradient(rgba(246, 246, 246, 1), rgba(246, 246, 246, 1)) !important; background-position: left top !important; background-size: auto !important; background-repeat: no-repeat !important; } /* HubSpot Styles (mobile) */ @media (max-width: 767px) { .dnd_area-row-1-padding { padding-top: 50px !important; padding-bottom: 50px !important; } } </style>  <script> var _hsp = window._hsp = window._hsp || []; window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} var useGoogleConsentModeV2 = true; var waitForUpdateMillis = 1000; if (!window._hsGoogleConsentRunOnce) { window._hsGoogleConsentRunOnce = true; gtag('consent', 'default', { 'ad_storage': 'denied', 'analytics_storage': 'denied', 'ad_user_data': 'denied', 'ad_personalization': 'denied', 'wait_for_update': waitForUpdateMillis }); if (useGoogleConsentModeV2) { _hsp.push(['useGoogleConsentModeV2']) } else { _hsp.push(['addPrivacyConsentListener', function(consent){ var hasAnalyticsConsent = consent && (consent.allowed || (consent.categories && consent.categories.analytics)); var hasAdsConsent = consent && (consent.allowed || (consent.categories && consent.categories.advertisement)); gtag('consent', 'update', { 'ad_storage': hasAdsConsent ? 'granted' : 'denied', 'analytics_storage': hasAnalyticsConsent ? 'granted' : 'denied', 'ad_user_data': hasAdsConsent ? 'granted' : 'denied', 'ad_personalization': hasAdsConsent ? 'granted' : 'denied' }); }]); } } gtag('js', new Date()); gtag('set', 'developer_id.dZTQ1Zm', true); gtag('config', 'G-6J80J11VFJ'); </script> <script async src="https://www.googletagmanager.com/gtag/js?id=G-6J80J11VFJ"></script>   <script> var _hsp = window._hsp = window._hsp || []; window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} var useGoogleConsentModeV2 = true; var waitForUpdateMillis = 1000; var hsLoadGtm = function loadGtm() { if(window._hsGtmLoadOnce) { return; } if (useGoogleConsentModeV2) { gtag('set','developer_id.dZTQ1Zm',true); gtag('consent', 'default', { 'ad_storage': 'denied', 'analytics_storage': 'denied', 'ad_user_data': 'denied', 'ad_personalization': 'denied', 'wait_for_update': waitForUpdateMillis }); _hsp.push(['useGoogleConsentModeV2']) } (function(w,d,s,l,i){w[l]=w[l]||[];w[l].push({'gtm.start': new Date().getTime(),event:'gtm.js'});var f=d.getElementsByTagName(s)[0], j=d.createElement(s),dl=l!='dataLayer'?'&l='+l:'';j.async=true;j.src= 'https://www.googletagmanager.com/gtm.js?id='+i+dl;f.parentNode.insertBefore(j,f); })(window,document,'script','dataLayer','GTM-WHMDSK'); window._hsGtmLoadOnce = true; }; _hsp.push(['addPrivacyConsentListener', function(consent){ if(consent.allowed || (consent.categories && consent.categories.analytics)){ hsLoadGtm(); } }]); </script>  <link rel="canonical" href="https://cratedb.com/database/sql-examples"> <meta property="og:url" content="https://cratedb.com/database/sql-examples"> <meta name="twitter:card" content="summary"> <meta http-equiv="content-language" content="en">    <meta property="og:image" content="https://cratedb.com/hubfs/cr-featured-image-23.jpg"> <meta property="og:image:width" content="1200"> <meta property="og:image:height" content="630"> <meta property="og:image:alt" content="CrateDB - Data Insights at Scale"> <meta name="generator" content="HubSpot"></head> <body class="">  <noscript><iframe src="https://www.googletagmanager.com/ns.html?id=GTM-WHMDSK" height="0" width="0" style="display:none;visibility:hidden"></iframe></noscript>  <div class="body-wrapper hs-content-id-134720698778 hs-site-page page "> <div id="hs_cos_wrapper_Header_Notif_Bar" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"></div> <div data-global-resource-path="sf2-crate/templates/partials/header-landify.html"> <header class="header" style="color: #ffffff ;" id="top"> <a href="#main-content" class="header__skip">Skip to content</a> <div class="header__container content-wrapper"> <div class="header__logo header__logo--main"> <div id="hs_cos_wrapper_site_logo" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module widget-type-logo" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"> <span id="hs_cos_wrapper_site_logo_hs_logo_widget" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_logo" style="" data-hs-cos-general-type="widget" data-hs-cos-type="logo"><a href="/" id="hs-link-site_logo_hs_logo_widget" style="border-width:0px;border:0px;"><img src="https://cratedb.com/hubfs/CrateDB-logo-blue-white.svg" class="hs-image-widget " height="204" style="height: auto;width:1433px;border-width:0px;border:0px;" width="1433" alt="CrateDB logo" title="CrateDB logo" loading="eager"></a></span> </div> </div> <div class="header_nav_column" style="text-align: center ;"> <div class="header_nav"> <div class="header--toggle header__navigation--toggle"></div> <div class="header__close--toggle"></div> <div class="header__navigation header--element"> <div id="hs_cos_wrapper_navigation-primary" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"> <nav aria-label="Main menu" class="navigation-primary"> <ul class="submenu level-1"> <li class="has-submenu menu-item hs-skip-lang-url-rewrite"> <a href="javascript:;" class="menu-link active-branch" title="Product">Product</a> <ul class="submenu level-2"> <li class="has-submenu menu-item hs-skip-lang-url-rewrite"> <a href="javascript:;" class="menu-link active-branch" title="Database">Database</a> <ul class="submenu level-3"> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/database" class="menu-link" title="Overview">Overview</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/database/cloud" class="menu-link" title="CrateDB Cloud">CrateDB Cloud</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/database/self-managed" class="menu-link" title="CrateDB Self-Managed">CrateDB Self-Managed</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/database/sql-examples" class="menu-link active-item" aria-current="page" title="SQL examples">SQL examples</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/database/integrations" class="menu-link" title="Integrations">Integrations</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/database/security" class="menu-link" title="Security">Security</a> </li> </ul> </li> <li class="has-submenu menu-item hs-skip-lang-url-rewrite"> <a href="javascript:;" class="menu-link" title="Data models">Data models</a> <ul class="submenu level-3"> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/data-model/time-series" class="menu-link" title="Time-series">Time-series</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/data-model/json" class="menu-link" title="Document/JSON">Document/JSON</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/data-model/vector" class="menu-link" title="Vector">Vector</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/data-model/full-text" class="menu-link" title="Full-text">Full-text</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/data-model/geospatial" class="menu-link" title="Spatial">Spatial</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/data-model/relational" class="menu-link" title="Relational">Relational</a> </li> </ul> </li> </ul> </li> <li class="has-submenu menu-item hs-skip-lang-url-rewrite"> <a href="javascript:;" class="menu-link" title="Use cases">Use cases</a> <ul class="submenu level-2"> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/use-cases/ai-ml-database" class="menu-link" title="AI/ML integration">AI/ML integration</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/use-cases/chatbots/" class="menu-link" title="AI-powered chatbots">AI-powered chatbots</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/use-cases/iot-database" class="menu-link" title="Internet of Things">Internet of Things</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/use-cases/digital-twins-database" class="menu-link" title="Digital twins">Digital twins</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/use-cases/geospatial-analytics" class="menu-link" title="Geospatial analytics">Geospatial analytics</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/use-cases/log-database" class="menu-link" title="Log & event analysis">Log & event analysis</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/use-cases/database-consolidation" class="menu-link" title="Database consolidation">Database consolidation</a> </li> </ul> </li> <li class="has-submenu menu-item hs-skip-lang-url-rewrite"> <a href="javascript:;" class="menu-link" title="Industries">Industries</a> <ul class="submenu level-2"> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/energy" class="menu-link" title="Energy">Energy</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/financial-services" class="menu-link" title="Financial Services">Financial Services</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/fmcg" class="menu-link" title="FMCG">FMCG</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/logistics" class="menu-link" title="Logistics">Logistics</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/manufacturing" class="menu-link" title="Manufacturing">Manufacturing</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/oil-gas-mining" class="menu-link" title="Oil, gas & mining">Oil, gas & mining</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/smart-cities" class="menu-link" title="Smart city solutions">Smart city solutions</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/tech-platforms" class="menu-link" title="Technology platforms">Technology platforms</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/telco" class="menu-link" title="Telco">Telco</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/industries/transportation" class="menu-link" title="Transportation">Transportation</a> </li> </ul> </li> <li class="has-submenu menu-item hs-skip-lang-url-rewrite"> <a href="javascript:;" class="menu-link" title="Resources">Resources</a> <ul class="submenu level-2"> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/stories" class="menu-link" title="User stories">User stories</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/academy" class="menu-link" title="Academy">Academy</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/library" class="menu-link" title="Asset library">Asset library</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/blog" class="menu-link" title="Blog">Blog</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/event" class="menu-link" title="Events">Events</a> </li> </ul> </li> <li class="has-submenu menu-item hs-skip-lang-url-rewrite"> <a href="javascript:;" class="menu-link" title="Developer">Developer</a> <ul class="submenu level-2"> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="/docs/guide/home/" class="menu-link" title="Documentation">Documentation</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/database/drivers-tools" class="menu-link" title="Drivers and tools">Drivers and tools</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/community" class="menu-link" title="Community">Community</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://github.com/crate/crate/" class="menu-link" target="_blank" rel="noopener" title="GitHub">GitHub</a> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/support" class="menu-link" title="Support">Support</a> </li> </ul> </li> <li class="no-submenu menu-item hs-skip-lang-url-rewrite"> <a href="https://cratedb.com/pricing" class="menu-link" title="Pricing">Pricing</a> </li> </ul> </nav></div> <div class="header__cta__column mb"> <div class="header__cta"> <div id="hs_cos_wrapper_module_161720035400929" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="ly-cta-div header-icons"> <div class="cr-gh-header"> <a href="https://github.com/crate/" title="Follow CrateDB on GitHub" rel="noreferrer" target="_blank"> <svg version="1.0" height="20" width="20" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 496 512" aria-labelledby="github5" role="img"><path d="M165.9 397.4c0 2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6 3.6zm-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0 6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3zm44.2-1.7c-2.9.7-4.9 2.6-4.6 4.9.3 2 2.9 3.3 5.9 2.6 2.9-.7 4.9-2.6 4.6-4.6-.3-1.9-3-3.2-5.9-2.9zM244.8 8C106.1 8 0 113.3 0 252c0 110.9 69.8 205.8 169.5 239.2 12.8 2.3 17.3-5.6 17.3-12.1 0-6.2-.3-40.4-.3-61.4 0 0-70 15-84.7-29.8 0 0-11.4-29.1-27.8-36.6 0 0-22.9-15.7 1.6-15.4 0 0 24.9 2 38.6 25.8 21.9 38.6 58.6 27.5 72.9 20.9 2.3-16 8.8-27.1 16-33.7-55.9-6.2-112.3-14.3-112.3-110.5 0-27.5 7.6-41.3 23.6-58.9-2.6-6.5-11.1-33.3 2.6-67.9 20.9-6.5 69 27 69 27 20-5.6 41.5-8.5 62.8-8.5s42.8 2.9 62.8 8.5c0 0 48.1-33.6 69-27 13.7 34.7 5.2 61.4 2.6 67.9 16 17.7 25.8 31.5 25.8 58.9 0 96.5-58.9 104.2-114.8 110.5 9.2 7.9 17 22.9 17 46.4 0 33.7-.3 75.4-.3 83.6 0 6.5 4.6 14.4 17.3 12.1C428.2 457.8 496 362.9 496 252 496 113.3 383.5 8 244.8 8zM97.2 352.9c-1.3 1-1 3.3.7 5.2 1.6 1.6 3.9 2.3 5.2 1 1.3-1 1-3.3-.7-5.2-1.6-1.6-3.9-2.3-5.2-1zm-10.8-8.1c-.7 1.3.3 2.9 2.3 3.9 1.6 1 3.6.7 4.3-.7.7-1.3-.3-2.9-2.3-3.9-2-.6-3.6-.3-4.3.7zm32.4 35.6c-1.6 1.3-1 4.3 1.3 6.2 2.3 2.3 5.2 2.6 6.5 1 1.3-1.3.7-4.3-1.3-6.2-2.2-2.3-5.2-2.6-6.5-1zm-11.4-14.7c-1.6 1-1.6 3.6 0 5.9 1.6 2.3 4.3 3.3 5.6 2.3 1.6-1.3 1.6-3.9 0-6.2-1.4-2.3-4-3.3-5.6-2z"></path></svg> </a> </div> <div class="cr-gh-header cr-search-header"> <a href="/search" class="search-btn menu-link" title="Search on cratedb.com"> <svg xmlns="http://www.w3.org/2000/svg" width="18" height="18" viewbox="0 0 512 512"><path d="M416 208c0 45.9-14.9 88.3-40 122.7L502.6 457.4c12.5 12.5 12.5 32.8 0 45.3s-32.8 12.5-45.3 0L330.7 376c-34.4 25.2-76.8 40-122.7 40C93.1 416 0 322.9 0 208S93.1 0 208 0S416 93.1 416 208zM208 352a144 144 0 1 0 0-288 144 144 0 1 0 0 288z"></path></svg> </a> </div> </div> <div class="ly-cta-div" style="margin-right:0;"> <div class="hs-web-interactive-inline" style="" data-hubspot-wrapper-cta-id="140734001567"> <a href="https://cta-service-cms2.hubspot.com/web-interactives/public/v1/track/click?encryptedPayload=AVxigLLN26yv8kQRp2WAoeOu9bNBbkLvTvRFUszq%2FYx5PHh5KDMckqiVw13YriNZl%2FdwfLQhIBdngdVl9Lx2IjudAdjrNLPdfhOwxbALtLSGiVSiJme4GD1HjmmjV%2BLWcWF9BI7hRc97dyKGXNt5JHxSRJoHlvLASH5kuFKzd92WI2YJWJb%2BFVwL%2FkowhjQ7LStla2KXRfev%2Bt5hHZ6w0AIdYbL%2Bw5TrWqqtyhZwdCt8vhgIqhu3f3Em59QiBrX4OknB3IvxW9zy0KkmILmwktRj%2BbA%3D&portalId=19927462" target="_blank" class="hs-inline-web-interactive-140734001567 cta-btn login-cta menu-link " data-hubspot-cta-id="140734001567"> Log In </a> </div> </div> <div class="ly-cta-div"> <div class="hs-web-interactive-inline" style="" data-hubspot-wrapper-cta-id="178336831256"> <a href="https://cta-service-cms2.hubspot.com/web-interactives/public/v1/track/click?encryptedPayload=AVxigLIJe21Dd%2BqMBXuwhUEwIxeKvQRteMuuvBgS0cjHs7WtGTrDP%2FOCVzg%2F92IOl9VbWfNtTIu%2FKC6mz8pqjKES7LqJM2rA5K6VXfMhvFaG4WQW2LFPe9AVNyYpYDsMuKe%2FlqIfbRifuiV29ECfFn%2BRra2Unad4SCh6ECtacrskeaY%2FWA%3D%3D&portalId=19927462" target="_blank" rel="noopener" class="hs-inline-web-interactive-178336831256 cr-btn-div cr-btn-2 " data-hubspot-cta-id="178336831256"> Start free </a> </div> </div> <style> .ly-cta-div a.cta-btn.login-cta{ color:#000000; background-color:#FFFFFF; font-size:16px; } .ly-cta-div a.cta-btn.signup-cta{ color:#000000; background-color:#FFFFFF; font-size:16px; } .ly-cta-div a.cta-btn{ display:inline-block; position:relative; padding:10px 25px; font-family:sans-serif; transition: all .4s ease; line-height:21px; border-radius:5px; cursor:pointer; text-decoration:none; font-weight: 600; } .ly-cta-div a.cta-btn:hover{ opacity:.7; } .cr-gh-header.cr-search-header { padding: 0; margin-right: 0; } div#hs_cos_wrapper_module_161720035400929 { display: inline-block; } </style></div> </div> </div> </div> </div> </div> <div class="header__cta__column lg"> <div class="header__cta"> <div id="hs_cos_wrapper_module_161720035400929" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="ly-cta-div header-icons"> <div class="cr-gh-header"> <a href="https://github.com/crate/" title="Follow CrateDB on GitHub" rel="noreferrer" target="_blank"> <svg version="1.0" height="20" width="20" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 496 512" aria-labelledby="github5" role="img"><path d="M165.9 397.4c0 2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6 3.6zm-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0 6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3zm44.2-1.7c-2.9.7-4.9 2.6-4.6 4.9.3 2 2.9 3.3 5.9 2.6 2.9-.7 4.9-2.6 4.6-4.6-.3-1.9-3-3.2-5.9-2.9zM244.8 8C106.1 8 0 113.3 0 252c0 110.9 69.8 205.8 169.5 239.2 12.8 2.3 17.3-5.6 17.3-12.1 0-6.2-.3-40.4-.3-61.4 0 0-70 15-84.7-29.8 0 0-11.4-29.1-27.8-36.6 0 0-22.9-15.7 1.6-15.4 0 0 24.9 2 38.6 25.8 21.9 38.6 58.6 27.5 72.9 20.9 2.3-16 8.8-27.1 16-33.7-55.9-6.2-112.3-14.3-112.3-110.5 0-27.5 7.6-41.3 23.6-58.9-2.6-6.5-11.1-33.3 2.6-67.9 20.9-6.5 69 27 69 27 20-5.6 41.5-8.5 62.8-8.5s42.8 2.9 62.8 8.5c0 0 48.1-33.6 69-27 13.7 34.7 5.2 61.4 2.6 67.9 16 17.7 25.8 31.5 25.8 58.9 0 96.5-58.9 104.2-114.8 110.5 9.2 7.9 17 22.9 17 46.4 0 33.7-.3 75.4-.3 83.6 0 6.5 4.6 14.4 17.3 12.1C428.2 457.8 496 362.9 496 252 496 113.3 383.5 8 244.8 8zM97.2 352.9c-1.3 1-1 3.3.7 5.2 1.6 1.6 3.9 2.3 5.2 1 1.3-1 1-3.3-.7-5.2-1.6-1.6-3.9-2.3-5.2-1zm-10.8-8.1c-.7 1.3.3 2.9 2.3 3.9 1.6 1 3.6.7 4.3-.7.7-1.3-.3-2.9-2.3-3.9-2-.6-3.6-.3-4.3.7zm32.4 35.6c-1.6 1.3-1 4.3 1.3 6.2 2.3 2.3 5.2 2.6 6.5 1 1.3-1.3.7-4.3-1.3-6.2-2.2-2.3-5.2-2.6-6.5-1zm-11.4-14.7c-1.6 1-1.6 3.6 0 5.9 1.6 2.3 4.3 3.3 5.6 2.3 1.6-1.3 1.6-3.9 0-6.2-1.4-2.3-4-3.3-5.6-2z"></path></svg> </a> </div> <div class="cr-gh-header cr-search-header"> <a href="/search" class="search-btn menu-link" title="Search on cratedb.com"> <svg xmlns="http://www.w3.org/2000/svg" width="18" height="18" viewbox="0 0 512 512"><path d="M416 208c0 45.9-14.9 88.3-40 122.7L502.6 457.4c12.5 12.5 12.5 32.8 0 45.3s-32.8 12.5-45.3 0L330.7 376c-34.4 25.2-76.8 40-122.7 40C93.1 416 0 322.9 0 208S93.1 0 208 0S416 93.1 416 208zM208 352a144 144 0 1 0 0-288 144 144 0 1 0 0 288z"></path></svg> </a> </div> </div> <div class="ly-cta-div" style="margin-right:0;"> <div class="hs-web-interactive-inline" style="" data-hubspot-wrapper-cta-id="140734001567"> <a href="https://cta-service-cms2.hubspot.com/web-interactives/public/v1/track/click?encryptedPayload=AVxigLLN26yv8kQRp2WAoeOu9bNBbkLvTvRFUszq%2FYx5PHh5KDMckqiVw13YriNZl%2FdwfLQhIBdngdVl9Lx2IjudAdjrNLPdfhOwxbALtLSGiVSiJme4GD1HjmmjV%2BLWcWF9BI7hRc97dyKGXNt5JHxSRJoHlvLASH5kuFKzd92WI2YJWJb%2BFVwL%2FkowhjQ7LStla2KXRfev%2Bt5hHZ6w0AIdYbL%2Bw5TrWqqtyhZwdCt8vhgIqhu3f3Em59QiBrX4OknB3IvxW9zy0KkmILmwktRj%2BbA%3D&portalId=19927462" target="_blank" class="hs-inline-web-interactive-140734001567 cta-btn login-cta menu-link " data-hubspot-cta-id="140734001567"> Log In </a> </div> </div> <div class="ly-cta-div"> <div class="hs-web-interactive-inline" style="" data-hubspot-wrapper-cta-id="178336831256"> <a href="https://cta-service-cms2.hubspot.com/web-interactives/public/v1/track/click?encryptedPayload=AVxigLIJe21Dd%2BqMBXuwhUEwIxeKvQRteMuuvBgS0cjHs7WtGTrDP%2FOCVzg%2F92IOl9VbWfNtTIu%2FKC6mz8pqjKES7LqJM2rA5K6VXfMhvFaG4WQW2LFPe9AVNyYpYDsMuKe%2FlqIfbRifuiV29ECfFn%2BRra2Unad4SCh6ECtacrskeaY%2FWA%3D%3D&portalId=19927462" target="_blank" rel="noopener" class="hs-inline-web-interactive-178336831256 cr-btn-div cr-btn-2 " data-hubspot-cta-id="178336831256"> Start free </a> </div> </div> <style> .ly-cta-div a.cta-btn.login-cta{ color:#000000; background-color:#FFFFFF; font-size:16px; } .ly-cta-div a.cta-btn.signup-cta{ color:#000000; background-color:#FFFFFF; font-size:16px; } .ly-cta-div a.cta-btn{ display:inline-block; position:relative; padding:10px 25px; font-family:sans-serif; transition: all .4s ease; line-height:21px; border-radius:5px; cursor:pointer; text-decoration:none; font-weight: 600; } .ly-cta-div a.cta-btn:hover{ opacity:.7; } .cr-gh-header.cr-search-header { padding: 0; margin-right: 0; } div#hs_cos_wrapper_module_161720035400929 { display: inline-block; } </style></div> </div> </div> </div> </header></div> <main id="main-content" class="body-container-wrapper"> <div class="container-fluid body-container body-container--about"> <div class="row-fluid-wrapper"> <div class="row-fluid"> <div class="span12 widget-span widget-type-cell " style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-1 dnd_area-row-0-force-full-width-section dnd_area-row-0-background-layers dnd-section dnd_area-row-0-background-image"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-2 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_16901864227893" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"> <div class="cr-clusters-items-wrapper cr-clusters-limit" id="module_16901864227893"> <style> #module_16901864227893.cr-clusters-items-wrapper { /*max-width: 1760px;*/ margin: 0 auto; } #module_16901864227893.cr-clusters-items-wrapper.cr-clusters-limit, .cr-meta-inner { max-width: 1200px; } #module_16901864227893 .cr-clusters-container.cr-customers-header-sub { max-width: 1200px; margin: 0 auto; } #module_16901864227893 .cr-clusters-container { display: flex; align-items: center; } #module_16901864227893 .cr-clusters-header { margin-bottom: 20px; display: flex; align-items: baseline; } #module_16901864227893 .cr-clusters-header h1 { letter-spacing: -2.04px; max-width: 620px; margin-right: 20px; margin-bottom: 0px; font-family: Montserrat, Inter, Arial, sans-serif !important; font-weight: 600; line-height: 1.2; } #module_16901864227893 .cr-customers-header-sub .cr-clusters-header h1 { max-width: 980px; } #module_16901864227893 .cr-customers-header-sub .cr-clusters-limit-width-headline.cr-clusters-header h1 { max-width: 700px; } #module_16901864227893.cr-clusters-limit .cr-customers-header-sub .cr-clusters-introtext { max-width: 950px; } #module_16901864227893:not(.cr-clusters-limit) .cr-customers-header-sub .cr-clusters-introtext { max-width: 100%; } #module_16901864227893 .cr-clusters-image { flex: 3; } #module_16901864227893 .cr-clusters-image img { height: auto; } #module_16901864227893 .cr-clusters-introtext, #module_16901864227893 .cr-clusters-introtext p, #module_16901864227893 .cr-clusters-introtext span { color: rgba(255, 255, 255, 1.0); font-size: 21px; margin-right: 100px; } #module_16901864227893 .cr-clusters-introtext { max-width: 680px; } #module_16901864227893 .cr-clusters-left { padding-top: 60px; flex: 3; } #module_16901864227893 .cr-header-section { display: block; font-size: 20px; font-weight: 600; color: #19C0EA; } #module_16901864227893 .cr-clusters-arrow { align-self: flex-end; } #module_16901864227893 .cr-clusters-arrow svg { max-width: 40px; height: auto; } .cr-landingpage-header { margin-bottom: 78px; } .cr-landingpage-header .dnd-column { padding: 0; } .cr-header-meta-bar { background: rgb(8, 29, 36); opacity: 1; padding: 20px 20px; position: absolute; bottom: -146px; left: 0; right: 0; margin-left: -20px; margin-right: -20px; } .cr-header-meta-bar > div > div { display: flex; font-family: Inter; font-weight: 600; font-size: 16px; line-height: 20px; letter-spacing: 0px; color: #FFFFFF; opacity: 1; } .cr-meta-inner { display: flex; flex-direction: row; align-items: center; justify-content: flex-start; margin: 0 auto; } .cr-meta-item { display: flex; align-items: center; margin-right: 30px; } .cr-meta-item img { margin-right: 10px; } .cr-header-speaker-image img { border-radius: 50%; max-width: 80px !important; } .cr-header-speaker-name { letter-spacing: -0.56px; color: #FFFFFF; font-size: 18px; line-height: 24px; font-weight: bold; font-family: Inter; text-align: center; margin-bottom: 2px; } .cr-header-speakers { display: flex; flex-direction: row; margin-top: 80px; } .cr-header-speakers-item:last-child { margin-left: 50px; } .cr-header-speaker-title { letter-spacing: -0.36px; color: #e3b15b; font-size: 16px; line-height: 20px; font-family: Inter; font-weight: 500; text-align: center; } .cr-header-speaker-image { text-align: center; } .cr-header-multipurpose { position: relative; } /*@media all and (min-width: 1200px) { .cr-header-speaker-image img { max-width: 100% !important; } }*/ @media all and (max-width: 1200px) { } @media all and (max-width: 980px) { #module_16901864227893 .cr-clusters-container { flex-direction: column; } #module_16901864227893 .cr-clusters-introtext { font-size: 18px; } #module_16901864227893 .cr-clusters-header h1 { width: auto; max-width: 100%; } #module_16901864227893 .cr-clusters-container { align-items: flex-start; } } @media all and (max-width: 767px) { #module_16901864227893 .cr-clusters-introtext { margin-right: 0; } .cr-header-meta-bar > div > div { flex-direction: column; text-align: center; font-size: 14px; } .cr-meta-item { margin-right: 10px; } .cr-header-speakers { margin-top: 40px; } } </style> <div class="cr-clusters-container cr-customers-header-sub"> <div class="cr-clusters-left"> <span class="cr-header-section">Product > Database</span> <div class="cr-clusters-header "> <h1 style="color: rgba(255, 255, 255, 1.0);">SQL Examples</h1> </div> </div> </div> </div> </div> </div> </div> </div> </div> </div> </div> <div class="row-fluid-wrapper row-depth-1 row-number-3 dnd_area-row-1-padding dnd_area-row-1-background-color dnd_area-row-1-max-width-section-centering dnd-section dnd_area-row-1-background-layers"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-4 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_16946054049385" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="horizontal-code-tab line-numbers"> <div class="tab tab-head"> <div class="heading-content"> <div style="text-align: left;"> </div> <h2 style="font-size: 52px;">Time-series data</h2> <p style="font-size: 18px;"><span style="color: #000000;"> </span></p> </div> <button class="tablinks module_16946054049385-codetabl" onclick="openCodeTabmodule_16946054049385(event, 'module_16946054049385-Codetab1')" id="module_16946054049385-defaultOpen"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Gap analysis</button> <button class="tablinks module_16946054049385-codetabl" onclick="openCodeTabmodule_16946054049385(event, 'module_16946054049385-Codetab2')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Number per day</button> <button class="tablinks module_16946054049385-codetabl" onclick="openCodeTabmodule_16946054049385(event, 'module_16946054049385-Codetab3')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Average temperature</button> </div> <div class="tab tab-body"> <div id="module_16946054049385-Codetab1" class="tabcontent module_16946054049385-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_16946054049385-pane-number1" role="tabpanel" id="module_16946054049385-Statement1"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> /* To identify gaps on the readings, the following queries generates a series * and by joining it with the original data, you can spot any gap */ with avg_battery AS ( SELECT battery_level, time FROM devices.readings WHERE device_id = 'demo000007' AND time > 1480118400000 AND time < 1480301200000 ORDER BY 2 ), all_hours AS ( SELECT generate_series(1480118430000,1480301200000,'30 second'::interval) AS generated_hours ) SELECT time, generated_hours, battery_level FROM all_hours LEFT JOIN avg_battery ON generated_hours = time ORDER BY 2 LIMIT 20; </code></pre> </code> </pre> </div> <div class="tab-pane module_16946054049385-pane-number1" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_16946054049385-Result1"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> +---------------+---------------+---------------+ | time | hours | battery_level | +---------------+---------------+---------------+ | 1480118430000 | 1480118430000 | 67 | | 1480118460000 | 1480118460000 | 66 | | 1480118490000 | 1480118490000 | 66 | | 1480118520000 | 1480118520000 | 66 | | 1480118550000 | 1480118550000 | 66 | | 1480118580000 | 1480118580000 | 66 | | 1480118610000 | 1480118610000 | 65 | | 1480118640000 | 1480118640000 | NULL | | 1480118670000 | 1480118670000 | 65 | | 1480118700000 | 1480118700000 | 65 | | 1480118730000 | 1480118730000 | 65 | | 1480118760000 | 1480118760000 | 65 | | 1480118790000 | 1480118790000 | 65 | | 1480118820000 | 1480118820000 | 65 | | 1480118850000 | 1480118850000 | 65 | | 1480118880000 | 1480118880000 | 65 | | 1480118910000 | 1480118910000 | 65 | | 1480118940000 | 1480118940000 | 65 | | 1480118970000 | 1480118970000 | NULL | | 1480119000000 | 1480119000000 | NULL | +---------------+---------------+---------------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_16946054049385-pill-number1 active" onclick="openPillmodule_169460540493851(event, 'module_16946054049385-Statement1')">Statement</button> <button class="tab-pill module_16946054049385-pill-number1" onclick="openPillmodule_169460540493851(event, 'module_16946054049385-Result1')">Result</button> </div> </div> </div> <script> function openPillmodule_169460540493851(evt1, module_16946054049385) { var i, tabcontentmodule_16946054049385, tablinksmodule_16946054049385; tabcontentmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-pane-number1"); for (i = 0; i < tabcontentmodule_16946054049385.length; i++) { tabcontentmodule_16946054049385[i].style.display = "none"; } tablinksmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-pill-number1"); for (i = 0; i < tablinksmodule_16946054049385.length; i++) { tablinksmodule_16946054049385[i].className = tablinksmodule_16946054049385[i].className.replace(" active", ""); } document.getElementById(module_16946054049385).style.display = "block"; evt1.currentTarget.className += " active"; } function openCodeTabmodule_16946054049385(evt1, module_16946054049385) { var i, tabcontentmodule_16946054049385, tablinksmodule_16946054049385; tabcontentmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-codetabc"); for (i = 0; i < tabcontentmodule_16946054049385.length; i++) { tabcontentmodule_16946054049385[i].style.display = "none"; } tablinksmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-codetabl"); for (i = 0; i < tablinksmodule_16946054049385.length; i++) { tablinksmodule_16946054049385[i].className = tablinksmodule_16946054049385[i].className.replace(" active", ""); } document.getElementById(module_16946054049385).style.display = "block"; evt1.currentTarget.className += " active"; } document.getElementById("module_16946054049385-defaultOpen").click(); </script> <div id="module_16946054049385-Codetab2" class="tabcontent module_16946054049385-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_16946054049385-pane-number2" role="tabpanel" id="module_16946054049385-Statement2"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> /* Based on device data, this query returns the number of battery charges * per day for a given device_id */ WITH aux_charging AS ( SELECT time, DATE_BIN('P1D'::INTERVAL,time,0) AS day, battery_status, LAG(battery_status) OVER (PARTITION BY device_id ORDER BY time) AS prev_battery_status FROM devices.readings WHERE device_id = 'demo000001' ORDER BY time ), count_start_charging AS ( SELECT day, (case when battery_status <> prev_battery_status then 1 else 0 end) AS start_charging FROM aux_charging ORDER BY 1 ) SELECT day, sum(start_charging) as charges_number FROM count_start_charging GROUP BY 1 ORDER BY 1; </code></pre> </code> </pre> </div> <div class="tab-pane module_16946054049385-pane-number2" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_16946054049385-Result2"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> +---------------+---------------+ | count_charges | day | +---------------+---------------+ | 2 | 1479168000000 | | 4 | 1479254400000 | | 2 | 1479340800000 | | 10 | 1479427200000 | | 7 | 1479600000000 | | 8 | 1479686400000 | | 6 | 1479772800000 | | 11 | 1479859200000 | | 5 | 1480032000000 | | 7 | 1480118400000 | | 6 | 1480204800000 | | 10 | 1480291200000 | | 3 | 1480464000000 | | 3 | 1480550400000 | | 7 | 1480636800000 | | 2 | 1480723200000 | +---------------+---------------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_16946054049385-pill-number2 active" onclick="openPillmodule_169460540493852(event, 'module_16946054049385-Statement2')">Statement</button> <button class="tab-pill module_16946054049385-pill-number2" onclick="openPillmodule_169460540493852(event, 'module_16946054049385-Result2')">Result</button> </div> </div> </div> <script> function openPillmodule_169460540493852(evt2, module_16946054049385) { var i, tabcontentmodule_16946054049385, tablinksmodule_16946054049385; tabcontentmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-pane-number2"); for (i = 0; i < tabcontentmodule_16946054049385.length; i++) { tabcontentmodule_16946054049385[i].style.display = "none"; } tablinksmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-pill-number2"); for (i = 0; i < tablinksmodule_16946054049385.length; i++) { tablinksmodule_16946054049385[i].className = tablinksmodule_16946054049385[i].className.replace(" active", ""); } document.getElementById(module_16946054049385).style.display = "block"; evt2.currentTarget.className += " active"; } function openCodeTabmodule_16946054049385(evt2, module_16946054049385) { var i, tabcontentmodule_16946054049385, tablinksmodule_16946054049385; tabcontentmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-codetabc"); for (i = 0; i < tabcontentmodule_16946054049385.length; i++) { tabcontentmodule_16946054049385[i].style.display = "none"; } tablinksmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-codetabl"); for (i = 0; i < tablinksmodule_16946054049385.length; i++) { tablinksmodule_16946054049385[i].className = tablinksmodule_16946054049385[i].className.replace(" active", ""); } document.getElementById(module_16946054049385).style.display = "block"; evt2.currentTarget.className += " active"; } document.getElementById("module_16946054049385-defaultOpen").click(); </script> <div id="module_16946054049385-Codetab3" class="tabcontent module_16946054049385-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_16946054049385-pane-number3" role="tabpanel" id="module_16946054049385-Statement3"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> /* Based on device data, this query returns the average of the battery temperature * for each OS version */ SELECT device_info['os_name'], avg(battery_temperature) FROM "devices"."readings" GROUP BY 1 LIMIT 100;</code></pre> </code> </pre> </div> <div class="tab-pane module_16946054049385-pane-number3" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_16946054049385-Result3"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> <span data-code-lang="sql" data-ds--code--code-block=""><code> +---------+--------------------------+ | os_name | avg(battery_temperature) | +---------+--------------------------+ | 4.4.4 | 90.85937893039049 | | 5.1.0 | 90.86754559738132 | | 6.0.1 | 90.84230101265824 | | 5.0.0 | 90.8574802739726 | +---------+--------------------------+ </code></span></code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_16946054049385-pill-number3 active" onclick="openPillmodule_169460540493853(event, 'module_16946054049385-Statement3')">Statement</button> <button class="tab-pill module_16946054049385-pill-number3" onclick="openPillmodule_169460540493853(event, 'module_16946054049385-Result3')">Result</button> </div> </div> </div> <script> function openPillmodule_169460540493853(evt3, module_16946054049385) { var i, tabcontentmodule_16946054049385, tablinksmodule_16946054049385; tabcontentmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-pane-number3"); for (i = 0; i < tabcontentmodule_16946054049385.length; i++) { tabcontentmodule_16946054049385[i].style.display = "none"; } tablinksmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-pill-number3"); for (i = 0; i < tablinksmodule_16946054049385.length; i++) { tablinksmodule_16946054049385[i].className = tablinksmodule_16946054049385[i].className.replace(" active", ""); } document.getElementById(module_16946054049385).style.display = "block"; evt3.currentTarget.className += " active"; } function openCodeTabmodule_16946054049385(evt3, module_16946054049385) { var i, tabcontentmodule_16946054049385, tablinksmodule_16946054049385; tabcontentmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-codetabc"); for (i = 0; i < tabcontentmodule_16946054049385.length; i++) { tabcontentmodule_16946054049385[i].style.display = "none"; } tablinksmodule_16946054049385 = document.getElementsByClassName("module_16946054049385-codetabl"); for (i = 0; i < tablinksmodule_16946054049385.length; i++) { tablinksmodule_16946054049385[i].className = tablinksmodule_16946054049385[i].className.replace(" active", ""); } document.getElementById(module_16946054049385).style.display = "block"; evt3.currentTarget.className += " active"; } document.getElementById("module_16946054049385-defaultOpen").click(); </script> </div> </div></div> </div> </div> </div> </div> </div> </div> <div class="row-fluid-wrapper row-depth-1 row-number-5 dnd-section"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-6 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_17018513636333" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="horizontal-code-tab line-numbers"> <div class="tab tab-head"> <div class="heading-content"> <div style="text-align: left;"> </div> <h2 style="font-size: 52px;">JSON data</h2> </div> <button class="tablinks module_17018513636333-codetabl" onclick="openCodeTabmodule_17018513636333(event, 'module_17018513636333-Codetab1')" id="module_17018513636333-defaultOpen"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Single INSERT statement</button> <button class="tablinks module_17018513636333-codetabl" onclick="openCodeTabmodule_17018513636333(event, 'module_17018513636333-Codetab2')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Querying with SELECT statement</button> <button class="tablinks module_17018513636333-codetabl" onclick="openCodeTabmodule_17018513636333(event, 'module_17018513636333-Codetab3')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Bulk INSERT statement</button> </div> <div class="tab tab-body"> <div id="module_17018513636333-Codetab1" class="tabcontent module_17018513636333-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513636333-pane-number1" role="tabpanel" id="module_17018513636333-Statement1"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> INSERT INTO community_areas(areanumber, name, details) VALUES ( 35, 'DOUGLAS', '{ "description": "The Douglas community area stretches...", "population": 20291, "zipcodes": [ "60609", "60616", "60653" ], "notableresident": { "name": "Ida B. Wells", "bio": "An American investigative journalist, educator and..." } }'::OBJECT ) </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513636333-pane-number1" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513636333-Result1"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"> </code> </pre> </div> </div> </div> <script> function openPillmodule_170185136363331(evt1, module_17018513636333) { var i, tabcontentmodule_17018513636333, tablinksmodule_17018513636333; tabcontentmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-pane-number1"); for (i = 0; i < tabcontentmodule_17018513636333.length; i++) { tabcontentmodule_17018513636333[i].style.display = "none"; } tablinksmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-pill-number1"); for (i = 0; i < tablinksmodule_17018513636333.length; i++) { tablinksmodule_17018513636333[i].className = tablinksmodule_17018513636333[i].className.replace(" active", ""); } document.getElementById(module_17018513636333).style.display = "block"; evt1.currentTarget.className += " active"; } function openCodeTabmodule_17018513636333(evt1, module_17018513636333) { var i, tabcontentmodule_17018513636333, tablinksmodule_17018513636333; tabcontentmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-codetabc"); for (i = 0; i < tabcontentmodule_17018513636333.length; i++) { tabcontentmodule_17018513636333[i].style.display = "none"; } tablinksmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-codetabl"); for (i = 0; i < tablinksmodule_17018513636333.length; i++) { tablinksmodule_17018513636333[i].className = tablinksmodule_17018513636333[i].className.replace(" active", ""); } document.getElementById(module_17018513636333).style.display = "block"; evt1.currentTarget.className += " active"; } document.getElementById("module_17018513636333-defaultOpen").click(); </script> <div id="module_17018513636333-Codetab2" class="tabcontent module_17018513636333-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513636333-pane-number2" role="tabpanel" id="module_17018513636333-Statement2"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* Retrieve fields from objects and slice arrays. */ SELECT name, array_slice(details['zipcodes'], 1, 1) as first_zip, details['population'] AS population, details['notableresident']['name'] AS famous_resident FROM community_areas WHERE areanumber = 35; </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513636333-pane-number2" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513636333-Result2"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"><pre><code> +---------+-----------+------------+-----------------+ | name | first_zip | population | famous_resident | +---------+-----------+------------+-----------------+ | DOUGLAS | ["60609"] | 20291 | Ida B. Wells | +---------+-----------+------------+-----------------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513636333-pill-number2 active" onclick="openPillmodule_170185136363332(event, 'module_17018513636333-Statement2')">Statement</button> <button class="tab-pill module_17018513636333-pill-number2" onclick="openPillmodule_170185136363332(event, 'module_17018513636333-Result2')">Result</button> </div> </div> </div> <script> function openPillmodule_170185136363332(evt2, module_17018513636333) { var i, tabcontentmodule_17018513636333, tablinksmodule_17018513636333; tabcontentmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-pane-number2"); for (i = 0; i < tabcontentmodule_17018513636333.length; i++) { tabcontentmodule_17018513636333[i].style.display = "none"; } tablinksmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-pill-number2"); for (i = 0; i < tablinksmodule_17018513636333.length; i++) { tablinksmodule_17018513636333[i].className = tablinksmodule_17018513636333[i].className.replace(" active", ""); } document.getElementById(module_17018513636333).style.display = "block"; evt2.currentTarget.className += " active"; } function openCodeTabmodule_17018513636333(evt2, module_17018513636333) { var i, tabcontentmodule_17018513636333, tablinksmodule_17018513636333; tabcontentmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-codetabc"); for (i = 0; i < tabcontentmodule_17018513636333.length; i++) { tabcontentmodule_17018513636333[i].style.display = "none"; } tablinksmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-codetabl"); for (i = 0; i < tablinksmodule_17018513636333.length; i++) { tablinksmodule_17018513636333[i].className = tablinksmodule_17018513636333[i].className.replace(" active", ""); } document.getElementById(module_17018513636333).style.display = "block"; evt2.currentTarget.className += " active"; } document.getElementById("module_17018513636333-defaultOpen").click(); </script> <div id="module_17018513636333-Codetab3" class="tabcontent module_17018513636333-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513636333-pane-number3" role="tabpanel" id="module_17018513636333-Statement3"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* Insert documents for two Chicago community areas. Dynamic object schemas allow these two documents to have different structures. */ INSERT INTO community_areas(areanumber, name, details) VALUES ( 35, 'DOUGLAS', '{ "description": "The Douglas community area stretches...", "population": 20291, "zipcodes": [ "60609", "60616", "60653" ], "notableresident": { "name": "Ida B. Wells", "bio": "An American investigative journalist, educator and..." } }'::OBJECT ), ( 41, 'HYDE PARK', '{ "description": "This community area is home to the Museum of Science...", "population": 29456, "notableresident": { "name": "Muhammad Ali", "born": 1942 } }'::OBJECT ); </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513636333-pane-number3" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513636333-Result3"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"> </code> </pre> </div> </div> </div> <script> function openPillmodule_170185136363333(evt3, module_17018513636333) { var i, tabcontentmodule_17018513636333, tablinksmodule_17018513636333; tabcontentmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-pane-number3"); for (i = 0; i < tabcontentmodule_17018513636333.length; i++) { tabcontentmodule_17018513636333[i].style.display = "none"; } tablinksmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-pill-number3"); for (i = 0; i < tablinksmodule_17018513636333.length; i++) { tablinksmodule_17018513636333[i].className = tablinksmodule_17018513636333[i].className.replace(" active", ""); } document.getElementById(module_17018513636333).style.display = "block"; evt3.currentTarget.className += " active"; } function openCodeTabmodule_17018513636333(evt3, module_17018513636333) { var i, tabcontentmodule_17018513636333, tablinksmodule_17018513636333; tabcontentmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-codetabc"); for (i = 0; i < tabcontentmodule_17018513636333.length; i++) { tabcontentmodule_17018513636333[i].style.display = "none"; } tablinksmodule_17018513636333 = document.getElementsByClassName("module_17018513636333-codetabl"); for (i = 0; i < tablinksmodule_17018513636333.length; i++) { tablinksmodule_17018513636333[i].className = tablinksmodule_17018513636333[i].className.replace(" active", ""); } document.getElementById(module_17018513636333).style.display = "block"; evt3.currentTarget.className += " active"; } document.getElementById("module_17018513636333-defaultOpen").click(); </script> </div> </div></div> </div> </div> </div> </div> </div> </div> <div class="row-fluid-wrapper row-depth-1 row-number-7 dnd-section dnd_area-row-3-background-layers dnd_area-row-3-background-color"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-8 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_17018513238503" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="horizontal-code-tab line-numbers"> <div class="tab tab-head"> <div class="heading-content"> <div style="text-align: left;"> </div> <h2 style="font-size: 52px;">Vector data</h2> </div> <button class="tablinks module_17018513238503-codetabl" onclick="openCodeTabmodule_17018513238503(event, 'module_17018513238503-Codetab1')" id="module_17018513238503-defaultOpen"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Compare how similar other data points are in comparison to an example text</button> </div> <div class="tab tab-body"> <div id="module_17018513238503-Codetab1" class="tabcontent module_17018513238503-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513238503-pane-number1" role="tabpanel" id="module_17018513238503-Statement1"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> SELECT text, _score FROM word_embeddings WHERE knn_match(embedding, (SELECT embedding FROM word_embeddings WHERE text ='Discovering galaxies'), 2) ORDER BY _score DESC </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513238503-pane-number1" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513238503-Result1"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> |------------------------|--------| | text | _score | |------------------------|--------| |Discovering galaxies |1 | |Discovering moon |0.952381| |Exploring the cosmos |0.840336| |Sending the mission |0.250626| |------------------------|--------| </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513238503-pill-number1 active" onclick="openPillmodule_170185132385031(event, 'module_17018513238503-Statement1')">Statement</button> <button class="tab-pill module_17018513238503-pill-number1" onclick="openPillmodule_170185132385031(event, 'module_17018513238503-Result1')">Result</button> </div> </div> </div> <script> function openPillmodule_170185132385031(evt1, module_17018513238503) { var i, tabcontentmodule_17018513238503, tablinksmodule_17018513238503; tabcontentmodule_17018513238503 = document.getElementsByClassName("module_17018513238503-pane-number1"); for (i = 0; i < tabcontentmodule_17018513238503.length; i++) { tabcontentmodule_17018513238503[i].style.display = "none"; } tablinksmodule_17018513238503 = document.getElementsByClassName("module_17018513238503-pill-number1"); for (i = 0; i < tablinksmodule_17018513238503.length; i++) { tablinksmodule_17018513238503[i].className = tablinksmodule_17018513238503[i].className.replace(" active", ""); } document.getElementById(module_17018513238503).style.display = "block"; evt1.currentTarget.className += " active"; } function openCodeTabmodule_17018513238503(evt1, module_17018513238503) { var i, tabcontentmodule_17018513238503, tablinksmodule_17018513238503; tabcontentmodule_17018513238503 = document.getElementsByClassName("module_17018513238503-codetabc"); for (i = 0; i < tabcontentmodule_17018513238503.length; i++) { tabcontentmodule_17018513238503[i].style.display = "none"; } tablinksmodule_17018513238503 = document.getElementsByClassName("module_17018513238503-codetabl"); for (i = 0; i < tablinksmodule_17018513238503.length; i++) { tablinksmodule_17018513238503[i].className = tablinksmodule_17018513238503[i].className.replace(" active", ""); } document.getElementById(module_17018513238503).style.display = "block"; evt1.currentTarget.className += " active"; } document.getElementById("module_17018513238503-defaultOpen").click(); </script> </div> </div></div> </div> </div> </div> </div> </div> </div> <div class="row-fluid-wrapper row-depth-1 row-number-9 dnd-section dnd_area-row-4-padding"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-10 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_17252941792325" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="cr-banner-items-wrapper" id="module_17252941792325"> <style> #module_17252941792325.cr-banner-items-wrapper { margin: 0 auto; background-image:url(https://cratedb.com/hubfs/cr-cta-banner-background.png); background-size: cover; background-position: right center; background-repeat: no-repeat; padding: 40px 30px; max-width: 100%; } #module_17252941792325 .cr-banner-header { display: flex; margin-right: 20px; flex-direction: column; } #module_17252941792325 .cr-banner-container { display: flex; justify-content: space-between; align-items: center; } #module_17252941792325 .cr-banner-items { margin-right: 5%; text-align: center; min-width: 280px; } #module_17252941792325 .cr-banner-header h2 { margin-bottom: 5px; font-family: Inter,Arial,sans-serif; font-weight: 700; letter-spacing: -0.4px; font-size: 36px; color: #19c0ea; margin-top: 0; color: var(--cr-primary-color); } #module_17252941792325 .cr-banner-header h3 { margin-bottom: 0; margin-top: 5px; font-family: Inter,Arial,sans-serif; font-weight: 600; letter-spacing: -0.4px; font-size: 30px; color: #ffffff; } @media all and (max-width: 980px) { #module_17252941792325 .cr-banner-container { flex-direction: column; } #module_17252941792325 .cr-banner-items { margin-right: 0; margin-top: 20px; } #module_17252941792325.cr-banner-items-wrapper { background-position: center center; } #module_17252941792325 .cr-banner-item { margin-right: 0; } #module_17252941792325 .cr-banner-header h2 { font-size: 26px; } #module_17252941792325 .cr-banner-header h3 { font-size: 22px; } } </style> <div class="cr-banner-container"> <div class="cr-banner-header"> <div class="cr-banner-headline"> <h2>CrateDB Academy – Free Courses</h2> </div> <div class="cr-banner-subheadline"> <h3>Enroll for free, Learn, and Get Certified</h3> </div> </div> <div class="cr-banner-items"> <div class="cr-banner-item"> <div class="hs-web-interactive-inline" style="" data-hubspot-wrapper-cta-id="170051388491"> <a href="https://cta-service-cms2.hubspot.com/web-interactives/public/v1/track/click?encryptedPayload=AVxigLJ2%2BEk9FG46HZQp3L2Dyz7RY98Kop%2FQIvNty4umylppPOlbK1HJb8h9YlDHBZ9dECKCi7YmaM1jVMEeLGEpldYRtMJQ5hwOVDmSLA3dWokxX8kXZ83YBfC%2FL1zBlbmQk8s2nI%2B%2FGVgg1M3BKlunL%2BCfbZ04auQhIrP8nblGJjYrRzlIECFy0iRVp4Ri&portalId=19927462" class="hs-inline-web-interactive-170051388491 hs-button " data-hubspot-cta-id="170051388491"> Enroll now </a> </div> </div> </div> </div> </div></div> </div> </div> </div> </div> </div> </div> <div class="row-fluid-wrapper row-depth-1 row-number-11 dnd-section"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-12 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_17018513754433" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="horizontal-code-tab line-numbers"> <div class="tab tab-head"> <div class="heading-content"> <div style="text-align: left;"> </div> <h2 style="font-size: 52px;">Full-text data</h2> </div> <button class="tablinks module_17018513754433-codetabl" onclick="openCodeTabmodule_17018513754433(event, 'module_17018513754433-Codetab1')" id="module_17018513754433-defaultOpen"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Defining a composite FULLTEXT INDEX with ANALYZER</button> <button class="tablinks module_17018513754433-codetabl" onclick="openCodeTabmodule_17018513754433(event, 'module_17018513754433-Codetab2')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Full-text search with boosting factors</button> <button class="tablinks module_17018513754433-codetabl" onclick="openCodeTabmodule_17018513754433(event, 'module_17018513754433-Codetab3')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Search of matching phrases and score filtering</button> <button class="tablinks module_17018513754433-codetabl" onclick="openCodeTabmodule_17018513754433(event, 'module_17018513754433-Codetab4')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Fuzzy Search</button> </div> <div class="tab tab-body"> <div id="module_17018513754433-Codetab1" class="tabcontent module_17018513754433-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513754433-pane-number1" role="tabpanel" id="module_17018513754433-Statement1"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* The table stores data about netflix movies and series. The composite fulltext * index is defined on two columns with the analyzer based on English language. */ CREATE TABLE "netflix_catalog" ( "show_id" TEXT PRIMARY KEY, "type" TEXT, "title" TEXT, "director" TEXT, "cast" ARRAY(TEXT), "country" TEXT, "date_added" TIMESTAMP, "release_year" TEXT, "rating" TEXT, "duration" TEXT, "listed_in" ARRAY(TEXT), "description" TEXT, INDEX title_director_description_ft using FULLTEXT (title, director, description) WITH (analyzer = 'english') ); </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513754433-pane-number1" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513754433-Result1"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513754433-pill-number1 active" onclick="openPillmodule_170185137544331(event, 'module_17018513754433-Statement1')">Statement</button> </div> </div> </div> <script> function openPillmodule_170185137544331(evt1, module_17018513754433) { var i, tabcontentmodule_17018513754433, tablinksmodule_17018513754433; tabcontentmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-pane-number1"); for (i = 0; i < tabcontentmodule_17018513754433.length; i++) { tabcontentmodule_17018513754433[i].style.display = "none"; } tablinksmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-pill-number1"); for (i = 0; i < tablinksmodule_17018513754433.length; i++) { tablinksmodule_17018513754433[i].className = tablinksmodule_17018513754433[i].className.replace(" active", ""); } document.getElementById(module_17018513754433).style.display = "block"; evt1.currentTarget.className += " active"; } function openCodeTabmodule_17018513754433(evt1, module_17018513754433) { var i, tabcontentmodule_17018513754433, tablinksmodule_17018513754433; tabcontentmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-codetabc"); for (i = 0; i < tabcontentmodule_17018513754433.length; i++) { tabcontentmodule_17018513754433[i].style.display = "none"; } tablinksmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-codetabl"); for (i = 0; i < tablinksmodule_17018513754433.length; i++) { tablinksmodule_17018513754433[i].className = tablinksmodule_17018513754433[i].className.replace(" active", ""); } document.getElementById(module_17018513754433).style.display = "block"; evt1.currentTarget.className += " active"; } document.getElementById("module_17018513754433-defaultOpen").click(); </script> <div id="module_17018513754433-Codetab2" class="tabcontent module_17018513754433-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513754433-pane-number2" role="tabpanel" id="module_17018513754433-Statement2"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* This query searches for records where the combined fields of title, director, and * description match the phrase 'mission impossible', with a different weights given to * title and description. The results are ordered by their relevance score in desc order. */ SELECT show_id, type, title, director, country, rating, description, _score FROM "netflix_catalog" WHERE MATCH((title_director_description_ft, title 1.5, description 1), 'mission impossible') ORDER BY _score DESC; </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513754433-pane-number2" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513754433-Result2"> <pre class="language- line-numbers no-whitespace-normalization"> <code class="language- no-whitespace-normalization"><pre class="language-yaml"><code class="language-yaml"> +---------+----------+------------------------+--------------------+---------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ | show_id | type | title | director | country | rating | description | _score | +---------+----------+------------------------+--------------------+---------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ | s1000 | Movie | Stowaway | Joe Penna | Germany, United States | TV-MA | A three-person crew on a mission to Mars faces an impossible choice when an unplanned passenger jeopardizes the lives of everyone on board. | 5.0469 | | s1768 | Movie | The Impossible | J.A. Bayona | Spain, Thailand, United States | PG-13 | Tracking one family's harrowing experiences, this gripping drama depicts the chaos generated by the massive 2004 tsunami in Southeast Asia. | 3.1496503 | | s7873 | TV Show | Rishta.com | NULL | India | TV-14 | Partners at an Indian matrimonial agency face endlessly challenging and often impossible demands as they help clients make the perfect match. | 3.131972 | | s3242 | TV Show | Nailed It! Holiday! | NULL | United States | TV-PG | It's the "Nailed It!" holiday special you've been waiting for, with missing ingredients, impossible asks and desserts that look delightfully sad. | 3.1265335 | | s1032 | Movie | Into the Beat | Stefan Westerwelle | Germany | TV-14 | A teen ballerina discovers hip-hop by chance and is faced with an impossible choice: Does she follow her parents' footsteps... or her newfound passion? | 2.9678147 | | s1583 | Movie | Triple 9 | John Hillcoat | United States | R | A group of dirty Atlanta cops blackmailed by the Russian mob plan a seemingly impossible heist that sets off an explosive chain reaction of violence. | 2.9678147 | | s848 | TV Show | Mad for Each Other | NULL | NULL | TV-MA | Bothered to realize they are next-door neighbors and share a psychiatrist, a man and a woman find it's impossible to stay out of each other's way. | 2.9385366 | +---------+----------+------------------------+--------------------+---------------------------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513754433-pill-number2 active" onclick="openPillmodule_170185137544332(event, 'module_17018513754433-Statement2')">Statement</button> <button class="tab-pill module_17018513754433-pill-number2" onclick="openPillmodule_170185137544332(event, 'module_17018513754433-Result2')">Result</button> </div> </div> </div> <script> function openPillmodule_170185137544332(evt2, module_17018513754433) { var i, tabcontentmodule_17018513754433, tablinksmodule_17018513754433; tabcontentmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-pane-number2"); for (i = 0; i < tabcontentmodule_17018513754433.length; i++) { tabcontentmodule_17018513754433[i].style.display = "none"; } tablinksmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-pill-number2"); for (i = 0; i < tablinksmodule_17018513754433.length; i++) { tablinksmodule_17018513754433[i].className = tablinksmodule_17018513754433[i].className.replace(" active", ""); } document.getElementById(module_17018513754433).style.display = "block"; evt2.currentTarget.className += " active"; } function openCodeTabmodule_17018513754433(evt2, module_17018513754433) { var i, tabcontentmodule_17018513754433, tablinksmodule_17018513754433; tabcontentmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-codetabc"); for (i = 0; i < tabcontentmodule_17018513754433.length; i++) { tabcontentmodule_17018513754433[i].style.display = "none"; } tablinksmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-codetabl"); for (i = 0; i < tablinksmodule_17018513754433.length; i++) { tablinksmodule_17018513754433[i].className = tablinksmodule_17018513754433[i].className.replace(" active", ""); } document.getElementById(module_17018513754433).style.display = "block"; evt2.currentTarget.className += " active"; } document.getElementById("module_17018513754433-defaultOpen").click(); </script> <div id="module_17018513754433-Codetab3" class="tabcontent module_17018513754433-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513754433-pane-number3" role="tabpanel" id="module_17018513754433-Statement3"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* The query searches records where the combined title, director, and description * fields phrase-prefix match 'award winning' with a relevance score of at least 10, * and orders the results by their relevance score in descending order. */ SELECT show_id, type, title, country, rating, duration, _score FROM "netflix_catalog" WHERE MATCH(title_director_description_ft, 'award winning') USING phrase_prefix AND _score >= 10 ORDER BY _score DESC; </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513754433-pane-number3" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513754433-Result3"> <pre class="language-yaml line-numbers no-whitespace-normalization"> <code class="language-yaml no-whitespace-normalization"><pre class="line-numbers"><code> +---------+----------+-----------------------------------------+-----------------+--------+------------+-----------+ | show_id | type | title | country | rating | duration | _score | | ------- | -------- | ----------------------------------------| --------------- | ------ | ---------- | --------- | | s2423 | TV Show | Queer Eye | United States | TV-14 | 5 Seasons | 56.835224 | | s5930 | Movie | Print the Legend | United States | TV-14 | 100 min | 56.835224 | | s849 | TV Show | Master of None | United States | TV-MA | 3 Seasons | 54.720444 | | s7369 | TV Show | Mad Men | United States | TV-14 | 7 Seasons | 53.722008 | | s4556 | Movie | The Meaning of Monty Python | United Kingdom | TV-MA | 60 min | 51.933487 | | s6342 | TV Show | Blue Planet II | United Kingdom | TV-G | 1 Season | 51.933487 | | s4162 | Movie | Surga Yang Tak Dirindukan 2 | Indonesia | TV-14 | 119 min | 50.937637 | | s5186 | TV Show | Alias Grace | Canada | TV-MA | 1 Season | 41.05056 | | s4184 | TV Show | The World's Most Extraordinary Homes | United Kingdom | TV-G | 3 Seasons | 39.506954 | | s6111 | Movie | Alejandro Sanz: What I Was Is What I Am | Spain | TV-MA | 102 min | 38.77893 | +---------+----------+-----------------------------------------+-----------------+--------+------------+-----------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513754433-pill-number3 active" onclick="openPillmodule_170185137544333(event, 'module_17018513754433-Statement3')">Statement</button> <button class="tab-pill module_17018513754433-pill-number3" onclick="openPillmodule_170185137544333(event, 'module_17018513754433-Result3')">Result</button> </div> </div> </div> <script> function openPillmodule_170185137544333(evt3, module_17018513754433) { var i, tabcontentmodule_17018513754433, tablinksmodule_17018513754433; tabcontentmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-pane-number3"); for (i = 0; i < tabcontentmodule_17018513754433.length; i++) { tabcontentmodule_17018513754433[i].style.display = "none"; } tablinksmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-pill-number3"); for (i = 0; i < tablinksmodule_17018513754433.length; i++) { tablinksmodule_17018513754433[i].className = tablinksmodule_17018513754433[i].className.replace(" active", ""); } document.getElementById(module_17018513754433).style.display = "block"; evt3.currentTarget.className += " active"; } function openCodeTabmodule_17018513754433(evt3, module_17018513754433) { var i, tabcontentmodule_17018513754433, tablinksmodule_17018513754433; tabcontentmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-codetabc"); for (i = 0; i < tabcontentmodule_17018513754433.length; i++) { tabcontentmodule_17018513754433[i].style.display = "none"; } tablinksmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-codetabl"); for (i = 0; i < tablinksmodule_17018513754433.length; i++) { tablinksmodule_17018513754433[i].className = tablinksmodule_17018513754433[i].className.replace(" active", ""); } document.getElementById(module_17018513754433).style.display = "block"; evt3.currentTarget.className += " active"; } document.getElementById("module_17018513754433-defaultOpen").click(); </script> <div id="module_17018513754433-Codetab4" class="tabcontent module_17018513754433-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513754433-pane-number4" role="tabpanel" id="module_17018513754433-Statement4"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* This query uses the composite index to match the term 'scence fction' * (with a likely typo in 'science fiction'), allowing for some fuzziness in matching, * using the English analyzer, requiring at least one term to match. */ SELECT show_id, title, country, description, _score FROM "netflix_catalog" WHERE MATCH(title_director_description_ft, 'scence fction') USING best_fields WITH ( fuzziness=1, analyzer='english', minimum_should_match=1, operator='or' ) ORDER BY _score DESC; </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513754433-pane-number4" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513754433-Result4"> <pre class="language-yaml line-numbers no-whitespace-normalization"> <code class="language-yaml no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> +---------+---------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ | show_id | title | country | description | _score | +---------+---------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ | s1604 | Alien Worlds | United Kingdom | Applying the laws of life on Earth to the rest of the galaxy, this series blends science fact and fiction to imagine alien life on other planets. | 4.052918 | | s2589 | Oh Yuck | Canada | Using a concoction of cartoons, comedy and live action, Dr. Yuck and his eccentric lab mates investigate the science behind the planet's ickiest things. | 3.8219523 | | s7848 | Red vs. Blue | United States | This parody of first-person shooter games, military life and science-fiction films centers on a civil war fought in the middle of a desolate canyon. | 3.8219523 | | s3054 | Border Security: America's Front Line | Canada | This reality series chronicles the behind-the-scenes action as U.S. Customs and Border Protection agents safeguard the nation's crossings and ports. | 3.5455647 | | s5870 | Turbo FAST | United States | Join Turbo and his posse, the Fast Action Stunt Team. Filled with comedy, action and fun, this series amps it up to the extreme. | 2.695207 | | s3574 | Our Planet - Behind The Scenes | United Kingdom | Years spent recording footage of creatures from every corner of the globe is bound to produce a bit of drama. Here's a behind-the-scenes look. | 2.5541513 | | s8016 | Sid the Science Kid | United States | Armed with a healthy sense of humor and the help of his teacher, friends and family, curious kid Sid tackles questions youngsters have about science. | 2.5541513 | | s4917 | Bill Nye: Science Guy | United States | The dynamic, bow-tied host behind the young adult science show faces climate-change skeptics as he demonstrates the importance of scientific evidence. | 2.49332 | +---------+---------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513754433-pill-number4 active" onclick="openPillmodule_170185137544334(event, 'module_17018513754433-Statement4')">Statement</button> <button class="tab-pill module_17018513754433-pill-number4" onclick="openPillmodule_170185137544334(event, 'module_17018513754433-Result4')">Result</button> </div> </div> </div> <script> function openPillmodule_170185137544334(evt4, module_17018513754433) { var i, tabcontentmodule_17018513754433, tablinksmodule_17018513754433; tabcontentmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-pane-number4"); for (i = 0; i < tabcontentmodule_17018513754433.length; i++) { tabcontentmodule_17018513754433[i].style.display = "none"; } tablinksmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-pill-number4"); for (i = 0; i < tablinksmodule_17018513754433.length; i++) { tablinksmodule_17018513754433[i].className = tablinksmodule_17018513754433[i].className.replace(" active", ""); } document.getElementById(module_17018513754433).style.display = "block"; evt4.currentTarget.className += " active"; } function openCodeTabmodule_17018513754433(evt4, module_17018513754433) { var i, tabcontentmodule_17018513754433, tablinksmodule_17018513754433; tabcontentmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-codetabc"); for (i = 0; i < tabcontentmodule_17018513754433.length; i++) { tabcontentmodule_17018513754433[i].style.display = "none"; } tablinksmodule_17018513754433 = document.getElementsByClassName("module_17018513754433-codetabl"); for (i = 0; i < tablinksmodule_17018513754433.length; i++) { tablinksmodule_17018513754433[i].className = tablinksmodule_17018513754433[i].className.replace(" active", ""); } document.getElementById(module_17018513754433).style.display = "block"; evt4.currentTarget.className += " active"; } document.getElementById("module_17018513754433-defaultOpen").click(); </script> </div> </div></div> </div> </div> </div> </div> </div> </div> <div class="row-fluid-wrapper row-depth-1 row-number-13 dnd-section dnd_area-row-6-background-layers dnd_area-row-6-background-color"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-14 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_16946054358867" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="horizontal-code-tab line-numbers"> <div class="tab tab-head"> <div class="heading-content"> <div style="text-align: left;"> </div> <h2 style="font-size: 52px;">Spatial data</h2> </div> <button class="tablinks module_16946054358867-codetabl" onclick="openCodeTabmodule_16946054358867(event, 'module_16946054358867-Codetab1')" id="module_16946054358867-defaultOpen"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> "distance" function</button> <button class="tablinks module_16946054358867-codetabl" onclick="openCodeTabmodule_16946054358867(event, 'module_16946054358867-Codetab2')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> "within" function</button> <button class="tablinks module_16946054358867-codetabl" onclick="openCodeTabmodule_16946054358867(event, 'module_16946054358867-Codetab3')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> "intersects" function</button> </div> <div class="tab tab-body"> <div id="module_16946054358867-Codetab1" class="tabcontent module_16946054358867-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_16946054358867-pane-number1" role="tabpanel" id="module_16946054358867-Statement1"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> /* Based on Chicago taxi trips, this query lists the longest 5 trips starting within 1km of the Shedd Aquarium and tells us the name of the community area that the trip ended within. */ SELECT tripid, miles, c.name as dropoff_area FROM taxi_rides t, community_areas c WHERE distance('POINT(-87.6118581 41.8672843)'::GEO_POINT, pickupcentroidlocation) < 1000 AND dropoffcommunityarea = c.areanumber ORDER BY miles DESC LIMIT 5; </code></pre> </code> </pre> </div> <div class="tab-pane module_16946054358867-pane-number1" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_16946054358867-Result1"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> +------------------------------------------+-------+--------------+ | tripid | miles | dropoff_area | +------------------------------------------+-------+--------------+ | e6d27fb09f7b17695a71666654c40fd56b79f472 | 46.5 | LOOP | | eb8dbd2d81faa21f8f6d80117748a8630c55d55c | 37.27 | OHARE | | 6aaa0d2aed0423f1321ff5df949afef0e3583d58 | 37.16 | OHARE | | e9ab078e5e6ed4d29cfbd310ee75d47236d3afbb | 32.03 | OHARE | | 329b4e6980070cab4c2af19ddbc7b73c1497a669 | 31.67 | OHARE | +------------------------------------------+-------+--------------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_16946054358867-pill-number1 active" onclick="openPillmodule_169460543588671(event, 'module_16946054358867-Statement1')">Statement</button> <button class="tab-pill module_16946054358867-pill-number1" onclick="openPillmodule_169460543588671(event, 'module_16946054358867-Result1')">Result</button> </div> </div> </div> <script> function openPillmodule_169460543588671(evt1, module_16946054358867) { var i, tabcontentmodule_16946054358867, tablinksmodule_16946054358867; tabcontentmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-pane-number1"); for (i = 0; i < tabcontentmodule_16946054358867.length; i++) { tabcontentmodule_16946054358867[i].style.display = "none"; } tablinksmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-pill-number1"); for (i = 0; i < tablinksmodule_16946054358867.length; i++) { tablinksmodule_16946054358867[i].className = tablinksmodule_16946054358867[i].className.replace(" active", ""); } document.getElementById(module_16946054358867).style.display = "block"; evt1.currentTarget.className += " active"; } function openCodeTabmodule_16946054358867(evt1, module_16946054358867) { var i, tabcontentmodule_16946054358867, tablinksmodule_16946054358867; tabcontentmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-codetabc"); for (i = 0; i < tabcontentmodule_16946054358867.length; i++) { tabcontentmodule_16946054358867[i].style.display = "none"; } tablinksmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-codetabl"); for (i = 0; i < tablinksmodule_16946054358867.length; i++) { tablinksmodule_16946054358867[i].className = tablinksmodule_16946054358867[i].className.replace(" active", ""); } document.getElementById(module_16946054358867).style.display = "block"; evt1.currentTarget.className += " active"; } document.getElementById("module_16946054358867-defaultOpen").click(); </script> <div id="module_16946054358867-Codetab2" class="tabcontent module_16946054358867-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_16946054358867-pane-number2" role="tabpanel" id="module_16946054358867-Statement2"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> /* Based on Chicago taxi trips, this query retuern the number of trips per day that started at O'Hare airport and were over 20 miles long. */ SELECT date_bin('24 hour'::interval, starttime, 0) AS day, count(*) as trips FROM taxi_rides WHERE within( pickupcentroidlocation, 'POLYGON ( (-87.92151405039768 42.008992106324456, -87.93962405297475 42.00147632076016, -87.94067626623455 41.95592273798644, -87.89918645000783 41.9491763875842, -87.88992003878232 41.95716729007498, -87.8821237584836 41.95716462062882, -87.87074008603852 41.967968085787476, -87.87727540517479 41.99772302560805, -87.89707897269737 42.009775581434354, -87.92151405039768 42.008992106324456) )' ) AND miles > 20 GROUP BY day ORDER BY day DESC; </code></pre> </code> </pre> </div> <div class="tab-pane module_16946054358867-pane-number2" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_16946054358867-Result2"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> +---------------+-------+ | day | trips | +---------------+-------+ | 1714521600000 | 24 | | 1714435200000 | 110 | | 1714348800000 | 109 | | 1714262400000 | 77 | | 1714176000000 | 52 | | 1714089600000 | 98 | | 1714003200000 | 113 | | 1713916800000 | 82 | | 1713830400000 | 59 | | 1713744000000 | 91 | | 1713657600000 | 69 | | 1713571200000 | 48 | | 1713484800000 | 82 | | 1713398400000 | 75 | | 1713312000000 | 80 | | 1713225600000 | 98 | | 1713139200000 | 104 | | 1713052800000 | 63 | | 1712966400000 | 75 | | 1712880000000 | 142 | | 1712793600000 | 169 | | 1712707200000 | 103 | | 1712620800000 | 154 | | 1712534400000 | 203 | | 1712448000000 | 98 | | 1712361600000 | 71 | | 1712275200000 | 80 | | 1712188800000 | 79 | | 1712102400000 | 72 | | 1712016000000 | 91 | | 1711929600000 | 39 | +---------------+-------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_16946054358867-pill-number2 active" onclick="openPillmodule_169460543588672(event, 'module_16946054358867-Statement2')">Statement</button> <button class="tab-pill module_16946054358867-pill-number2" onclick="openPillmodule_169460543588672(event, 'module_16946054358867-Result2')">Result</button> </div> </div> </div> <script> function openPillmodule_169460543588672(evt2, module_16946054358867) { var i, tabcontentmodule_16946054358867, tablinksmodule_16946054358867; tabcontentmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-pane-number2"); for (i = 0; i < tabcontentmodule_16946054358867.length; i++) { tabcontentmodule_16946054358867[i].style.display = "none"; } tablinksmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-pill-number2"); for (i = 0; i < tablinksmodule_16946054358867.length; i++) { tablinksmodule_16946054358867[i].className = tablinksmodule_16946054358867[i].className.replace(" active", ""); } document.getElementById(module_16946054358867).style.display = "block"; evt2.currentTarget.className += " active"; } function openCodeTabmodule_16946054358867(evt2, module_16946054358867) { var i, tabcontentmodule_16946054358867, tablinksmodule_16946054358867; tabcontentmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-codetabc"); for (i = 0; i < tabcontentmodule_16946054358867.length; i++) { tabcontentmodule_16946054358867[i].style.display = "none"; } tablinksmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-codetabl"); for (i = 0; i < tablinksmodule_16946054358867.length; i++) { tablinksmodule_16946054358867[i].className = tablinksmodule_16946054358867[i].className.replace(" active", ""); } document.getElementById(module_16946054358867).style.display = "block"; evt2.currentTarget.className += " active"; } document.getElementById("module_16946054358867-defaultOpen").click(); </script> <div id="module_16946054358867-Codetab3" class="tabcontent module_16946054358867-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_16946054358867-pane-number3" role="tabpanel" id="module_16946054358867-Statement3"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> /* Which communities of more than 30,000 people does a flight from O'Hare to Midway airport pass over? */ SELECT name, details['population'] AS population FROM community_areas WHERE intersects ( 'LINESTRING (-87.90753846894022 41.9807787186139, -87.72939445102593 41.97508268795004, -87.55763552335945 41.97982941555023, -87.47846040428642 41.92427055787562, -87.51102436455034 41.837749425166244, -87.59225264192574 41.80057450627759, -87.64801197528328 41.770600684095974, -87.69988112259261 41.7819633786835, -87.7530469985847 41.78583107072223)', boundaries ) AND details['population'] > 30000 ORDER BY population DESC; </code></pre> </code> </pre> </div> <div class="tab-pane module_16946054358867-pane-number3" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_16946054358867-Result3"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> +------------------------+------------+ | name | population | +------------------------+------------+ | UPTOWN | 57182 | | EDGEWATER | 56296 | | CHICAGO LAWN | 55931 | | ALBANY PARK | 48396 | | LINCOLN SQUARE | 40494 | | NORWOOD PARK | 38303 | | GARFIELD RIDGE | 35439 | | WEST LAWN | 33662 | | GREATER GRAND CROSSING | 31471 | +------------------------+------------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_16946054358867-pill-number3 active" onclick="openPillmodule_169460543588673(event, 'module_16946054358867-Statement3')">Statement</button> <button class="tab-pill module_16946054358867-pill-number3" onclick="openPillmodule_169460543588673(event, 'module_16946054358867-Result3')">Result</button> </div> </div> </div> <script> function openPillmodule_169460543588673(evt3, module_16946054358867) { var i, tabcontentmodule_16946054358867, tablinksmodule_16946054358867; tabcontentmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-pane-number3"); for (i = 0; i < tabcontentmodule_16946054358867.length; i++) { tabcontentmodule_16946054358867[i].style.display = "none"; } tablinksmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-pill-number3"); for (i = 0; i < tablinksmodule_16946054358867.length; i++) { tablinksmodule_16946054358867[i].className = tablinksmodule_16946054358867[i].className.replace(" active", ""); } document.getElementById(module_16946054358867).style.display = "block"; evt3.currentTarget.className += " active"; } function openCodeTabmodule_16946054358867(evt3, module_16946054358867) { var i, tabcontentmodule_16946054358867, tablinksmodule_16946054358867; tabcontentmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-codetabc"); for (i = 0; i < tabcontentmodule_16946054358867.length; i++) { tabcontentmodule_16946054358867[i].style.display = "none"; } tablinksmodule_16946054358867 = document.getElementsByClassName("module_16946054358867-codetabl"); for (i = 0; i < tablinksmodule_16946054358867.length; i++) { tablinksmodule_16946054358867[i].className = tablinksmodule_16946054358867[i].className.replace(" active", ""); } document.getElementById(module_16946054358867).style.display = "block"; evt3.currentTarget.className += " active"; } document.getElementById("module_16946054358867-defaultOpen").click(); </script> </div> </div></div> </div> </div> </div> </div> </div> </div> <div class="row-fluid-wrapper row-depth-1 row-number-15 dnd-section"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-16 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_17018513698783" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="horizontal-code-tab line-numbers"> <div class="tab tab-head"> <div class="heading-content"> <div style="text-align: left;"> </div> <h2 style="font-size: 52px;">Table data</h2> </div> <button class="tablinks module_17018513698783-codetabl" onclick="openCodeTabmodule_17018513698783(event, 'module_17018513698783-Codetab1')" id="module_17018513698783-defaultOpen"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Create a table with readings data</button> <button class="tablinks module_17018513698783-codetabl" onclick="openCodeTabmodule_17018513698783(event, 'module_17018513698783-Codetab2')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Select 10 entries from data in joined table </button> <button class="tablinks module_17018513698783-codetabl" onclick="openCodeTabmodule_17018513698783(event, 'module_17018513698783-Codetab3')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Select latest readings</button> <button class="tablinks module_17018513698783-codetabl" onclick="openCodeTabmodule_17018513698783(event, 'module_17018513698783-Codetab4')"> <svg xmlns="http://www.w3.org/2000/svg" viewbox="0 0 69.969 56.814" width="20" height="17"> <g id="subheader" transform="translate(96.312 88.344) rotate(180)"> <g id="back" transform="translate(32 34.358)"> <path id="arrow_tip" data-name="Path 42" d="M0,36.174H36.174V0" transform="translate(51.158 25.579) rotate(135)" fill="none" stroke="#18b8e7" stroke-width="8"></path> <line id="arrow_line" data-name="Line 1" x2="63.051" transform="translate(1.261 25.579)" fill="none" stroke="#18b8e7" stroke-linejoin="round" stroke-width="8"></line> </g> </g> </svg> Use of CTE (Common Table Expression)</button> </div> <div class="tab tab-body"> <div id="module_17018513698783-Codetab1" class="tabcontent module_17018513698783-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513698783-pane-number1" role="tabpanel" id="module_17018513698783-Statement1"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* Table that stores device reading information. * Composite primary key defined on two columns. */ CREATE TABLE IF NOT EXISTS "doc"."devices_readings" ( "ts" TIMESTAMP WITH TIME ZONE, "device_id" TEXT, "battery" OBJECT(DYNAMIC) AS ( "level" BIGINT, "status" TEXT, "temperature" DOUBLE PRECISION ), "cpu" OBJECT(DYNAMIC) AS ( "avg_1min" DOUBLE PRECISION, "avg_5min" DOUBLE PRECISION, "avg_15min" DOUBLE PRECISION ) PRIMARY KEY (ts, device_id) ) CLUSTERED INTO 6 SHARDS </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513698783-pane-number1" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513698783-Result1"> <pre class="language-sql line-numbers no-whitespace-normalization"> <code class="language-sql no-whitespace-normalization"> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513698783-pill-number1 active" onclick="openPillmodule_170185136987831(event, 'module_17018513698783-Statement1')">Statement</button> </div> </div> </div> <script> function openPillmodule_170185136987831(evt1, module_17018513698783) { var i, tabcontentmodule_17018513698783, tablinksmodule_17018513698783; tabcontentmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-pane-number1"); for (i = 0; i < tabcontentmodule_17018513698783.length; i++) { tabcontentmodule_17018513698783[i].style.display = "none"; } tablinksmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-pill-number1"); for (i = 0; i < tablinksmodule_17018513698783.length; i++) { tablinksmodule_17018513698783[i].className = tablinksmodule_17018513698783[i].className.replace(" active", ""); } document.getElementById(module_17018513698783).style.display = "block"; evt1.currentTarget.className += " active"; } function openCodeTabmodule_17018513698783(evt1, module_17018513698783) { var i, tabcontentmodule_17018513698783, tablinksmodule_17018513698783; tabcontentmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-codetabc"); for (i = 0; i < tabcontentmodule_17018513698783.length; i++) { tabcontentmodule_17018513698783[i].style.display = "none"; } tablinksmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-codetabl"); for (i = 0; i < tablinksmodule_17018513698783.length; i++) { tablinksmodule_17018513698783[i].className = tablinksmodule_17018513698783[i].className.replace(" active", ""); } document.getElementById(module_17018513698783).style.display = "block"; evt1.currentTarget.className += " active"; } document.getElementById("module_17018513698783-defaultOpen").click(); </script> <div id="module_17018513698783-Codetab2" class="tabcontent module_17018513698783-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513698783-pane-number2" role="tabpanel" id="module_17018513698783-Statement2"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* The query returns 10 records from two joined tables: * devices_readings and devices_info. */ SELECT r.device_id, r.ts, r.battery['level'], r.battery['status'], i.manufacturer, i.os_name FROM devices_readings r JOIN devices_info i ON r.device_id = i.device_id LIMIT 10; </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513698783-pane-number2" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513698783-Result2"> <pre class="language- line-numbers no-whitespace-normalization"> <code class="language- no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> +------------+---------------+------------------+-------------------+--------------+---------+ | device_id | ts | battery['level'] | battery['status'] | manufacturer | os_name | +------------+---------------+------------------+-------------------+--------------+---------+ | demo000025 | 1479920640000 | 26 | discharging | iobeam | 6.0.1 | | demo000025 | 1480209300000 | 52 | discharging | iobeam | 6.0.1 | | demo000025 | 1479663270000 | 53 | charging | iobeam | 6.0.1 | | demo000062 | 1479461430000 | 14 | discharging | iobeam | 6.0.1 | | demo000067 | 1480606380000 | 42 | discharging | iobeam | 5.0.0 | | demo000081 | 1479387000000 | 85 | discharging | iobeam | 6.0.1 | | demo000081 | 1480166250000 | 50 | discharging | iobeam | 6.0.1 | | demo000081 | 1479369300000 | 35 | discharging | iobeam | 6.0.1 | | demo000087 | 1479885600000 | 72 | discharging | iobeam | 5.0.0 | | demo000087 | 1480322430000 | 65 | discharging | iobeam | 5.0.0 | +------------+---------------+------------------+-------------------+--------------+---------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513698783-pill-number2 active" onclick="openPillmodule_170185136987832(event, 'module_17018513698783-Statement2')">Statement</button> <button class="tab-pill module_17018513698783-pill-number2" onclick="openPillmodule_170185136987832(event, 'module_17018513698783-Result2')">Result</button> </div> </div> </div> <script> function openPillmodule_170185136987832(evt2, module_17018513698783) { var i, tabcontentmodule_17018513698783, tablinksmodule_17018513698783; tabcontentmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-pane-number2"); for (i = 0; i < tabcontentmodule_17018513698783.length; i++) { tabcontentmodule_17018513698783[i].style.display = "none"; } tablinksmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-pill-number2"); for (i = 0; i < tablinksmodule_17018513698783.length; i++) { tablinksmodule_17018513698783[i].className = tablinksmodule_17018513698783[i].className.replace(" active", ""); } document.getElementById(module_17018513698783).style.display = "block"; evt2.currentTarget.className += " active"; } function openCodeTabmodule_17018513698783(evt2, module_17018513698783) { var i, tabcontentmodule_17018513698783, tablinksmodule_17018513698783; tabcontentmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-codetabc"); for (i = 0; i < tabcontentmodule_17018513698783.length; i++) { tabcontentmodule_17018513698783[i].style.display = "none"; } tablinksmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-codetabl"); for (i = 0; i < tablinksmodule_17018513698783.length; i++) { tablinksmodule_17018513698783[i].className = tablinksmodule_17018513698783[i].className.replace(" active", ""); } document.getElementById(module_17018513698783).style.display = "block"; evt2.currentTarget.className += " active"; } document.getElementById("module_17018513698783-defaultOpen").click(); </script> <div id="module_17018513698783-Codetab3" class="tabcontent module_17018513698783-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513698783-pane-number3" role="tabpanel" id="module_17018513698783-Statement3"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* * Based on sensor data, this query calculates: * - time-buckets of 10 seconds * - different aggregations per time-bucket and host group */ SELECT FLOOR(EXTRACT(epoch FROM m.timestamp) / 10) * 10 AS period, h.host_group, MIN(m.fields['usage_user']) AS "min", AVG(m.fields['usage_user']) AS "avg", MAX(m.fields['usage_user']) AS "max" FROM telegraf.metrics m LEFT JOIN telegraf.hosts h ON h.host_name = m.tags['host'] WHERE tags['cpu'] = 'cpu-total' AND m.timestamp > NOW() - '150 seconds'::INTERVAL GROUP BY 1, 2 ORDER BY 1 DESC; </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513698783-pane-number3" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513698783-Result3"> <pre class="language-yaml line-numbers no-whitespace-normalization"> <code class="language-yaml no-whitespace-normalization"><pre class="language-sql line-numbers"><code class="language-sql"> +------------+---------+-------+--------------+ | device_id | battery | cpu | manufacturer | +------------+---------+-------+--------------+ | demo002053 | 96.6 | 6.17 | iobeam | | demo002380 | 91.7 | 8.71 | iobeam | | demo000391 | 96.3 | 21.0 | iobeam | | demo000472 | 90.4 | 8.23 | iobeam | | demo000157 | 93.9 | 4.17 | iobeam | | demo000307 | 89.2 | 6.05 | iobeam | | demo002227 | 88.9 | 5.24 | iobeam | | demo000703 | 93.2 | 4.17 | iobeam | | demo001792 | 89.3 | 4.18 | iobeam | | demo001780 | 87.9 | 4.95 | iobeam | +------------+---------+-------+--------------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513698783-pill-number3 active" onclick="openPillmodule_170185136987833(event, 'module_17018513698783-Statement3')">Statement</button> <button class="tab-pill module_17018513698783-pill-number3" onclick="openPillmodule_170185136987833(event, 'module_17018513698783-Result3')">Result</button> </div> </div> </div> <script> function openPillmodule_170185136987833(evt3, module_17018513698783) { var i, tabcontentmodule_17018513698783, tablinksmodule_17018513698783; tabcontentmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-pane-number3"); for (i = 0; i < tabcontentmodule_17018513698783.length; i++) { tabcontentmodule_17018513698783[i].style.display = "none"; } tablinksmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-pill-number3"); for (i = 0; i < tablinksmodule_17018513698783.length; i++) { tablinksmodule_17018513698783[i].className = tablinksmodule_17018513698783[i].className.replace(" active", ""); } document.getElementById(module_17018513698783).style.display = "block"; evt3.currentTarget.className += " active"; } function openCodeTabmodule_17018513698783(evt3, module_17018513698783) { var i, tabcontentmodule_17018513698783, tablinksmodule_17018513698783; tabcontentmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-codetabc"); for (i = 0; i < tabcontentmodule_17018513698783.length; i++) { tabcontentmodule_17018513698783[i].style.display = "none"; } tablinksmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-codetabl"); for (i = 0; i < tablinksmodule_17018513698783.length; i++) { tablinksmodule_17018513698783[i].className = tablinksmodule_17018513698783[i].className.replace(" active", ""); } document.getElementById(module_17018513698783).style.display = "block"; evt3.currentTarget.className += " active"; } document.getElementById("module_17018513698783-defaultOpen").click(); </script> <div id="module_17018513698783-Codetab4" class="tabcontent module_17018513698783-codetabc"> <div class="tab-container"> <div class="tab-pane show active module_17018513698783-pane-number4" role="tabpanel" id="module_17018513698783-Statement4"> <pre class="language-sql line-numbers no-whitespace-normalization" data-start="1"> <code class="language-sql no-whitespace-normalization"><pre><code> /* The query provides a detailed analysis of the battery performance * (both level and temperature) for devices with specific API versions, * while focusing only on recent data. */ WITH max_timestamp AS ( SELECT MAX(ts) AS max_ts FROM doc.devices_readings ), device_readings_agg AS ( SELECT r.device_id, AVG(r.battery['level']) AS avg_battery_level, AVG(r.battery['temperature']) AS avg_battery_temperature FROM devices_readings r, max_timestamp m WHERE r.ts >= m.max_ts - INTERVAL '1 week' GROUP BY r.device_id ), device_model_info AS ( SELECT device_id, manufacturer, model, api_version FROM devices_info WHERE api_version BETWEEN 21 AND 25 ) SELECT info.manufacturer, info.model, info.api_version, AVG(read.avg_battery_level) AS model_avg_battery_level, AVG(read.avg_battery_temperature) AS model_avg_battery_temperature, COUNT(*) AS readings_count FROM device_readings_agg read JOIN device_model_info info ON read.device_id = info.device_id GROUP BY info.manufacturer, info.model, info.api_version ORDER BY model_avg_battery_level DESC; </code></pre> </code> </pre> </div> <div class="tab-pane module_17018513698783-pane-number4" role="tabpanel" aria-labelledby="cr-la-1-tab" id="module_17018513698783-Result4"> <pre class="language-yaml line-numbers no-whitespace-normalization"> <code class="language-yaml no-whitespace-normalization"><pre><code> +--------------+---------+-------------+-------------------------+-------------------------------+----------------+ | manufacturer | model | api_version | model_avg_battery_level | model_avg_battery_temperature | readings_count | +--------------+---------+-------------+-------------------------+-------------------------------+----------------+ | iobeam | focus | 22 | 51.812515496614985 | 90.95264431126454 | 37 | | iobeam | pinto | 23 | 51.320575821166265 | 90.94536040662497 | 29 | | iobeam | mustang | 21 | 51.12593768085889 | 90.84655787872552 | 57 | | iobeam | mustang | 22 | 50.92397024012975 | 90.8132448654412 | 29 | | iobeam | focus | 23 | 50.3493936780697 | 90.86938401742282 | 27 | | iobeam | mustang | 23 | 50.226325680521306 | 90.82969886537008 | 28 | | iobeam | pinto | 21 | 50.11437819309262 | 90.81538121980917 | 60 | | iobeam | focus | 21 | 49.60331350481256 | 90.87625045994797 | 59 | | iobeam | pinto | 22 | 49.54924258513566 | 90.84554594103246 | 29 | +--------------+---------+-------------+-------------------------+-------------------------------+----------------+ </code></pre> </code> </pre> </div> <div class="tab-pills statement-result"> <button class="tab-pill module_17018513698783-pill-number4 active" onclick="openPillmodule_170185136987834(event, 'module_17018513698783-Statement4')">Statement</button> <button class="tab-pill module_17018513698783-pill-number4" onclick="openPillmodule_170185136987834(event, 'module_17018513698783-Result4')">Result</button> </div> </div> </div> <script> function openPillmodule_170185136987834(evt4, module_17018513698783) { var i, tabcontentmodule_17018513698783, tablinksmodule_17018513698783; tabcontentmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-pane-number4"); for (i = 0; i < tabcontentmodule_17018513698783.length; i++) { tabcontentmodule_17018513698783[i].style.display = "none"; } tablinksmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-pill-number4"); for (i = 0; i < tablinksmodule_17018513698783.length; i++) { tablinksmodule_17018513698783[i].className = tablinksmodule_17018513698783[i].className.replace(" active", ""); } document.getElementById(module_17018513698783).style.display = "block"; evt4.currentTarget.className += " active"; } function openCodeTabmodule_17018513698783(evt4, module_17018513698783) { var i, tabcontentmodule_17018513698783, tablinksmodule_17018513698783; tabcontentmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-codetabc"); for (i = 0; i < tabcontentmodule_17018513698783.length; i++) { tabcontentmodule_17018513698783[i].style.display = "none"; } tablinksmodule_17018513698783 = document.getElementsByClassName("module_17018513698783-codetabl"); for (i = 0; i < tablinksmodule_17018513698783.length; i++) { tablinksmodule_17018513698783[i].className = tablinksmodule_17018513698783[i].className.replace(" active", ""); } document.getElementById(module_17018513698783).style.display = "block"; evt4.currentTarget.className += " active"; } document.getElementById("module_17018513698783-defaultOpen").click(); </script> </div> </div></div> </div> </div> </div> </div> </div> </div> <div class="row-fluid-wrapper row-depth-1 row-number-17 dnd-section dnd_area-row-8-background-layers dnd_area-row-8-background-color"> <div class="row-fluid "> <div class="span12 widget-span widget-type-cell dnd-column" style="" data-widget-type="cell" data-x="0" data-w="12"> <div class="row-fluid-wrapper row-depth-1 row-number-18 dnd-row"> <div class="row-fluid "> <div class="span12 widget-span widget-type-custom_widget dnd-module" style="" data-widget-type="custom_widget" data-x="0" data-w="12"> <div id="hs_cos_wrapper_module_16932226417395" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><div class="cr-getstarted-items-wrapper" id="module_16932226417395"> <style> #module_16932226417395.cr-getstarted-items-wrapper { /*max-width: 1760px;*/ margin: 0 auto; } #module_16932226417395 .cr-getstarted-header { margin-bottom: 32px; display: flex; align-items: center; margin-right: 20px; } #module_16932226417395 .cr-getstarted-item { margin-right: 20px; } #module_16932226417395 .cr-getstarted-items { display: flex; flex-direction: row; } #module_16932226417395 .cr-getstarted-header h2 { letter-spacing: -2.04px; color: #0A2327; margin-right: 30px; margin-bottom: 0; font-family: Montserrat, Inter, Arial, sans-serif !important; font-weight: 600; } #module_16932226417395 .cr-getstarted-header svg { height: 50px; width: auto; } #module_16932226417395 .cr-getstarted-container.cr-gestarted-inline { display: flex; align-items: center; /*justify-content: space-between;*/ } #module_16932226417395 .cr-gestarted-inline .cr-getstarted-header, #module_16932226417395 .cr-gestarted-inline .cr-getstarted-header h2 { margin-bottom: 0px; } @media all and (max-width: 767px) { #module_16932226417395 .cr-getstarted-container.cr-gestarted-inline { display: block; } #module_16932226417395 .cr-gestarted-inline .cr-getstarted-header { margin-bottom: 20px; } #module_16932226417395 .cr-gestarted-inline .cr-getstarted-header h2 { margin-bottom: 12px; } #module_16932226417395 .cr-getstarted-header svg { height: 30px; margin-bottom: 12px; } } </style> <div class="cr-getstarted-container cr-gestarted-inline"> <div class="cr-getstarted-header"> <h2>Interested in learning more?</h2> </div> <div class="cr-getstarted-items"> <div class="cr-getstarted-item"> <div class="hs-web-interactive-inline" style="" data-hubspot-wrapper-cta-id="136730818150"> <a href="https://cta-service-cms2.hubspot.com/web-interactives/public/v1/track/click?encryptedPayload=AVxigLLIr4fYoPCyVRZQ1SUhUztfucvhrslwvtiNDIGQfxCrfh2nXVCY%2BdRAKRQJxMyvWFcd3lCBmn6b0%2BwLMLb6i0xooumY8PPWIkSW%2BjigK%2FKFOQw8n0rdQ51nc7aMHdq0tlUhxAsQ7Myt610rW3HAbpqOMS3PyptCPYsoibg9Eg%3D%3D&portalId=19927462" class="hs-inline-web-interactive-136730818150 hs-button " data-hubspot-cta-id="136730818150"> Contact us </a> </div> </div> </div> </div> </div></div> </div> </div> </div> </div> </div> </div> </div> </div> </div> </div> <input type="hidden" name="breadcrumb" value=""> </main> <div data-global-resource-path="sf2-crate/templates/partials/footer-landify.html"> <footer class="footer" id="cr-footer"> <div class="footer-overlay-color" style="background-color: transparent;"> <div class="footer__container content-wrapper footer_firstrow"> <div class="row-fluid"> <div class="span12"> <div class="row-fluid footer-widget"> <div class="span3 footer-widget-content"> <div class="footer_logo"> <img src="https://cratedb.com/hubfs/19927462/CrateDB-logo-blue-white.svg" class="hs-image-widget" loading="lazy" style="width:154px;border-width:0px;border:0px;" alt="CrateDB Logo" title="White CrateDB Logo" width="154" height="22"> </div> <div class="footer__social"> <div id="hs_cos_wrapper_footer_social" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><style> .social-links { -webkit-box-align: center; -ms-flex-align: center; align-items: center; display: -webkit-box; display: -ms-flexbox; display: flex; -ms-flex-wrap: wrap; flex-wrap: wrap; -webkit-box-pack: center; -ms-flex-pack: center; justify-content: center; } .social-links__icon { background-color: #494A52; border-radius: 50%; display: -webkit-inline-box; display: -ms-inline-flexbox; display: inline-flex; height: 40px; margin: 0 5px; position: relative; width: 40px; } .social-links__icon:hover, .social-links__icon:focus { background-color: #21222A; } .social-links__icon:active { background-color: #71727A; } .social-links__icon svg { fill: #FFF; height: 15px; left: 50%; position: absolute; top: 50%; -webkit-transform: translate(-50%, -50%); -ms-transform: translate(-50%, -50%); transform: translate(-50%, -50%); width: auto; } .social-links__icon:hover svg, .social-links__icon:focus svg, .social-links__icon:active svg { fill: #FFF; } /* Overrides 2021 -j */ .social-links__icon { height: 25px; width: 25px; margin: 0 8px; } </style> <div class="social-links"> <a class="social-links__link nav-link" href="https://twitter.com/cratedb" target="_blank" rel="noopener"> <span id="hs_cos_wrapper_footer_social_" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_icon social-links__icon" style="" data-hs-cos-general-type="widget" data-hs-cos-type="icon"><svg version="1.0" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 512 512" aria-labelledby="X Twitter1" role="img"><title id="X Twitter1">Follow us on Twitter</title><g id="X Twitter1_layer"><path d="M389.2 48h70.6L305.6 224.2 487 464H345L233.7 318.6 106.5 464H35.8L200.7 275.5 26.8 48H172.4L272.9 180.9 389.2 48zM364.4 421.8h39.1L151.1 88h-42L364.4 421.8z" /></g></svg></span> <span class="injected hidden">Follow us on Twitter</span> </a> <a class="social-links__link nav-link" href="https://www.linkedin.com/company/crateio/" target="_blank" rel="noopener"> <span id="hs_cos_wrapper_footer_social__2" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_icon social-links__icon" style="" data-hs-cos-general-type="widget" data-hs-cos-type="icon"><svg version="1.0" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 448 512" aria-labelledby="LinkedIn In2" role="img"><title id="LinkedIn In2">Follow us on GitHub</title><g id="LinkedIn In2_layer"><path d="M100.28 448H7.4V148.9h92.88zM53.79 108.1C24.09 108.1 0 83.5 0 53.8a53.79 53.79 0 0 1 107.58 0c0 29.7-24.1 54.3-53.79 54.3zM447.9 448h-92.68V302.4c0-34.7-.7-79.2-48.29-79.2-48.29 0-55.69 37.7-55.69 76.7V448h-92.78V148.9h89.08v40.8h1.3c12.4-23.5 42.69-48.3 87.88-48.3 94 0 111.28 61.9 111.28 142.3V448z" /></g></svg></span> <span class="injected hidden">Follow us on GitHub</span> </a> <a class="social-links__link nav-link" href="https://www.youtube.com/@CrateDB" target="_blank" rel="noopener"> <span id="hs_cos_wrapper_footer_social__3" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_icon social-links__icon" style="" data-hs-cos-general-type="widget" data-hs-cos-type="icon"><svg version="1.0" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 576 512" aria-labelledby="YouTube3" role="img"><title id="YouTube3">Follow us on YouTube</title><g id="YouTube3_layer"><path d="M549.655 124.083c-6.281-23.65-24.787-42.276-48.284-48.597C458.781 64 288 64 288 64S117.22 64 74.629 75.486c-23.497 6.322-42.003 24.947-48.284 48.597-11.412 42.867-11.412 132.305-11.412 132.305s0 89.438 11.412 132.305c6.281 23.65 24.787 41.5 48.284 47.821C117.22 448 288 448 288 448s170.78 0 213.371-11.486c23.497-6.321 42.003-24.171 48.284-47.821 11.412-42.867 11.412-132.305 11.412-132.305s0-89.438-11.412-132.305zm-317.51 213.508V175.185l142.739 81.205-142.739 81.201z" /></g></svg></span> <span class="injected hidden">Follow us on YouTube</span> </a> <a class="social-links__link nav-link" href="https://github.com/crate/crate" target="_blank" rel="noopener"> <span id="hs_cos_wrapper_footer_social__4" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_icon social-links__icon" style="" data-hs-cos-general-type="widget" data-hs-cos-type="icon"><svg version="1.0" xmlns="http://www.w3.org/2000/svg" viewbox="0 0 496 512" aria-labelledby="GitHub4" role="img"><title id="GitHub4">Follow us on GitHub</title><g id="GitHub4_layer"><path d="M165.9 397.4c0 2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6 3.6zm-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0 6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3zm44.2-1.7c-2.9.7-4.9 2.6-4.6 4.9.3 2 2.9 3.3 5.9 2.6 2.9-.7 4.9-2.6 4.6-4.6-.3-1.9-3-3.2-5.9-2.9zM244.8 8C106.1 8 0 113.3 0 252c0 110.9 69.8 205.8 169.5 239.2 12.8 2.3 17.3-5.6 17.3-12.1 0-6.2-.3-40.4-.3-61.4 0 0-70 15-84.7-29.8 0 0-11.4-29.1-27.8-36.6 0 0-22.9-15.7 1.6-15.4 0 0 24.9 2 38.6 25.8 21.9 38.6 58.6 27.5 72.9 20.9 2.3-16 8.8-27.1 16-33.7-55.9-6.2-112.3-14.3-112.3-110.5 0-27.5 7.6-41.3 23.6-58.9-2.6-6.5-11.1-33.3 2.6-67.9 20.9-6.5 69 27 69 27 20-5.6 41.5-8.5 62.8-8.5s42.8 2.9 62.8 8.5c0 0 48.1-33.6 69-27 13.7 34.7 5.2 61.4 2.6 67.9 16 17.7 25.8 31.5 25.8 58.9 0 96.5-58.9 104.2-114.8 110.5 9.2 7.9 17 22.9 17 46.4 0 33.7-.3 75.4-.3 83.6 0 6.5 4.6 14.4 17.3 12.1C428.2 457.8 496 362.9 496 252 496 113.3 383.5 8 244.8 8zM97.2 352.9c-1.3 1-1 3.3.7 5.2 1.6 1.6 3.9 2.3 5.2 1 1.3-1 1-3.3-.7-5.2-1.6-1.6-3.9-2.3-5.2-1zm-10.8-8.1c-.7 1.3.3 2.9 2.3 3.9 1.6 1 3.6.7 4.3-.7.7-1.3-.3-2.9-2.3-3.9-2-.6-3.6-.3-4.3.7zm32.4 35.6c-1.6 1.3-1 4.3 1.3 6.2 2.3 2.3 5.2 2.6 6.5 1 1.3-1.3.7-4.3-1.3-6.2-2.2-2.3-5.2-2.6-6.5-1zm-11.4-14.7c-1.6 1-1.6 3.6 0 5.9 1.6 2.3 4.3 3.3 5.6 2.3 1.6-1.3 1.6-3.9 0-6.2-1.4-2.3-4-3.3-5.6-2z" /></g></svg></span> <span class="injected hidden">Follow us on GitHub</span> </a> </div></div> </div> </div> <div id="hs_cos_wrapper_module_16142721378942" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"> <div class="span3 footer-widget-content"> <h3 class="footer-menu-title"> Company </h3> <div id="hs_cos_wrapper_module_16142721378942_" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_inline_rich_text" style="" data-hs-cos-general-type="widget" data-hs-cos-type="inline_rich_text" data-hs-cos-field="footer_menu_1.footer_menu"><ul> <li><a class="nav-link" href="https://cratedb.com/company/leadership" rel="noopener">Leadership</a></li> <li><a class="nav-link" href="https://cratedb.com/company/team" rel="noopener">Team</a></li> <li><a class="nav-link" href="https://cratedb.com/company/investors" rel="noopener">Investors</a></li> <li><a class="nav-link" href="https://cratedb.com/company/career" rel="noopener">Career</a></li> <li><a class="nav-link" href="https://cratedb.com/event" rel="noopener">Events</a></li> <li><a class="nav-link" href="https://cratedb.com/press" rel="noopener">Newsroom</a></li> <li><a class="nav-link" href="https://cratedb.com/company/media-kit" rel="noopener">Media kit</a></li> </ul></div> </div> <div class="span3 footer-widget-content"> <h3 class="footer-menu-title"> Ecosystem </h3> <div id="hs_cos_wrapper_module_16142721378942_" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_inline_rich_text" style="" data-hs-cos-general-type="widget" data-hs-cos-type="inline_rich_text" data-hs-cos-field="footer_menu_2.footer_menu"><ul> <li><a class="nav-link" href="https://cratedb.com/partners" rel="noopener">Partners</a></li> <li><a class="nav-link" href="https://cratedb.com/product/editions/startups" rel="noopener">Startups</a></li> <li><a class="nav-link" href="https://cratedb.com/integrations" rel="noopener">Integrations</a></li> </ul></div> </div> <div class="span3 footer-widget-content"> <h3 class="footer-menu-title"> Contact </h3> <div id="hs_cos_wrapper_module_16142721378942_" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_inline_rich_text" style="" data-hs-cos-general-type="widget" data-hs-cos-type="inline_rich_text" data-hs-cos-field="footer_menu_3.footer_menu"><ul> <li><a class="nav-link" href="https://cratedb.com/contact" rel="noopener">Contact us</a></li> <li><a class="nav-link" href="https://cratedb.com/contact/offices" rel="noopener">Offices</a></li> <li><a class="nav-link" href="https://cratedb.com/database/security#contact" rel="noopener">Security</a></li> <li><a class="nav-link" href="https://cratedb.com/support" rel="noopener">Support</a></li> </ul></div> </div> </div> </div> </div> </div> </div> </div> <div class="footer-second-row-overlay" style="background-color: transparent;"> <div class="footer__container content-wrapper footer_copyright"> <div class="footer__copyright"> <div id="hs_cos_wrapper_footer_copyright_text" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_module widget-type-text" style="" data-hs-cos-general-type="widget" data-hs-cos-type="module"><span id="hs_cos_wrapper_footer_copyright_text_" class="hs_cos_wrapper hs_cos_wrapper_widget hs_cos_wrapper_type_text" style="" data-hs-cos-general-type="widget" data-hs-cos-type="text">© 2024 CrateDB. All rights reserved.</span></div> </div> <div class="hs_cos_wrapper_widget"> <ul> <li> | </li> <li><a href="/legal" class="nav-link">Legal</a> | </li> <li><a href="/legal/privacy-policy" class="nav-link">Privacy Policy</a> | </li> <li><a href="/legal/imprint" class="nav-link">Imprint</a></li> </ul> </div> </div> </div> </footer> </div> </div>  <script defer src="/hs/hsstatic/content-cwv-embed/static-1.1293/embed.js"></script> <script src="https://cratedb.com/hs-fs/hub/19927462/hub_generated/template_assets/52694035002/1710167292109/sf2-crate/js/main.min.js"></script> <script src="https://cratedb.com/hs-fs/hub/19927462/hub_generated/template_assets/103135878028/1676853638526/sf2-crate/js/prism-core.min.js"></script> <script src="https://cratedb.com/hs-fs/hub/19927462/hub_generated/template_assets/103135878026/1676853638465/sf2-crate/js/prism-autoloader.min.js"></script> <script src="https://cratedb.com/hs-fs/hub/19927462/hub_generated/template_assets/53645044755/1688038947921/sf2-crate/js/prism.min.js"></script> <script> var hsVars = hsVars || {}; hsVars['language'] = 'en'; </script> <script src="/hs/hsstatic/cos-i18n/static-1.53/bundles/project.js"></script> <script src="https://cratedb.com/hs-fs/hub/19927462/hub_generated/module_assets/52694764069/1701097527096/module_52694764069_menu-section.min.js"></script>  <script defer src="https://js.hubspot.com/web-interactives-embed.js" type="text/javascript" id="hubspot-web-interactives-loader" data-loader="hs-previewer" data-hsjs-portal="19927462" data-hsjs-env="prod" data-hsjs-hublet="na1"></script>   <script type="text/javascript"> var _hsq = _hsq || []; _hsq.push(["setContentType", "standard-page"]); _hsq.push(["setCanonicalUrl", "https:\/\/cratedb.com\/database\/sql-examples"]); _hsq.push(["setPageId", "134720698778"]); _hsq.push(["setContentMetadata", { "contentPageId": 134720698778, "legacyPageId": "134720698778", "contentFolderId": null, "contentGroupId": null, "abTestId": null, "languageVariantId": 134720698778, "languageCode": "en", }]); </script> <script type="text/javascript" id="hs-script-loader" async defer src="/hs/scriptloader/19927462.js"></script>  <script type="text/javascript"> var hsVars = { render_id: "e9f441b8-ee81-495d-9e7d-b94495667f80", ticks: 1732645688940, page_id: 134720698778, content_group_id: 0, portal_id: 19927462, app_hs_base_url: "https://app.hubspot.com", cp_hs_base_url: "https://cp.hubspot.com", language: "en", analytics_page_type: "standard-page", scp_content_type: "", analytics_page_id: "134720698778", category_id: 1, folder_id: 0, is_hubspot_user: false } </script> <script defer src="/hs/hsstatic/HubspotToolsMenu/static-1.354/js/index.js"></script> <script type="text/javascript"> // Log the analytics category consent status of the current visitor to the console var _hsp = window._hsp = window._hsp || []; // analytics _hsp.push(['addPrivacyConsentListener', function(consent) { if (consent.categories.analytics == true) { //console.log("analytics cookies enabled"); /* clarity */ (function(c,l,a,r,i,t,y){ c[a]=c[a]||function(){(c[a].q=c[a].q||[]).push(arguments)}; t=l.createElement(r);t.async=1;t.src="https://www.clarity.ms/tag/"+i; y=l.getElementsByTagName(r)[0];y.parentNode.insertBefore(t,y); })(window, document, "clarity", "script", "dyojgmowl8"); } }]); // advertisement _hsp.push(['addPrivacyConsentListener', function(consent) { if (consent.categories.advertisement == true) { //console.log("advertisement cookies enabled"); //Google Tag Manager set ad_storage window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} // Update Tagmanager to grant consent for Ads trigger gtag('consent', 'update', { 'ad_user_data': 'granted', 'ad_personalization': 'granted', 'ad_storage': 'granted', 'analytics_storage': 'granted' }); //End Google Tag Manager } }]); // functionality _hsp.push(['addPrivacyConsentListener', function(consent) { if (consent.categories.functionality == true) { //console.log("functionality cookies enabled"); } }]); </script> </body></html>

CINXE.COM

SQL query examples