{"id":278755,"date":"2026-11-03T15:46:01","date_gmt":"2026-11-03T10:16:01","guid":{"rendered":"https:\/\/imarticus.org\/blog\/?p=278755"},"modified":"2026-11-03T15:46:05","modified_gmt":"2026-11-03T10:16:05","slug":"sql-functions","status":"publish","type":"post","link":"https:\/\/imarticus.org\/blog\/sql-functions\/","title":{"rendered":"SQL Functions That Make Data Analysis Easier"},"content":{"rendered":"\n<style>\n  .wrap {\n    margin: 24px 0;\n    width: 100%;\n    box-sizing: border-box;\n  }\n  .card {\n    box-sizing: border-box;\n    width: 100%;\n    max-width: 720px;\n    margin: 0 auto;\n    background: #eaf6ec;\n    border: 1.5px solid #b0dabb;\n    border-radius: clamp(16px, 3vw, 26px);\n    padding: clamp(22px, 4.5vw, 44px);\n    text-align: center;\n    font-family: 'Manrope', Arial, Helvetica, sans-serif;\n  }\n  .card * {\n    box-sizing: border-box;\n  }\n  .badge {\n    display: block;\n    text-align: left;\n    margin: 0 0 10px 0;\n    color: #2f7a4d;\n    font-weight: 800;\n    font-size: 15px;\n    letter-spacing: 0.04em;\n    text-transform: uppercase;\n  }\n  .card p {\n    font-size: clamp(14.5px, 1.8vw, 16.5px);\n    line-height: 1.7;\n    color: #2c3e33;\n    margin: 0;\n    text-align: left;\n    overflow-wrap: break-word; \/* Prevents long text from pushing width *\/\n  }\n  .card strong {\n    color: #1f3d2a;\n    font-weight: 700;\n  }\n<\/style>\n\n<div class=\"wrap\">\n  <div class=\"card\">\n    <div class=\"badge\">Summary<\/div>\n    <p>\nSQL functions are small built-in commands. You give them some data, and they give you an answer, like a total, an average, a cleaned name, or a rank. There are a few kinds. COUNT, SUM and AVG are aggregate functions, and they turn many rows into one number. UPPER, ROUND and the date functions work on one value at a time. Window functions do a calculation across related rows but still show every row. Then there are user-defined functions, which you write yourself when nothing built-in fits. Knowing these, you&#8217;ll be able to perform most of the reporting tasks that you&#8217;ll encounter in a typical data job.\n        <\/p>\n  <\/div>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\">If someone asks me what to learn first with SQL, I would tell him to learn SQL functions first. Not joins, not fancy tricks. Functions. As the day you learn them, it&#8217;s the day your queries begin giving you real answers. Totals, rankings, clean names, monthly numbers &#8211; all of it comes from functions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In this blog, I&#8217;ll go through each type one by one, with a small example for each one. Nothing heavy, I promise. And if you want to make a career out of this, have a look at our <a href=\"https:\/\/imarticus.org\/postgraduate-program-in-data-science-analytics\/\"><strong>data analytics course<\/strong><\/a><strong>.<\/strong> It&#8217;s made for people who want a job at the end, so SQL is taught the way companies actually use it.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>SQL Functions Meaning<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A function is a command that performs a task and returns a result. The task the function performs varies, and so does the format of the data you provide to the function. For example, to find the average sales for a particular year, you\u2019d use a function that looks like this: AVG(sales for year). And to find the number of sales for that year, you\u2019d use this function: COUNT(sales for year). SQL functions, in general, are very similar to standard computer programming functions. However, you can easily perform tasks with SQL functions without writing very lengthy and complicated queries.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Take UPPER(&#8216;mumbai&#8217;). You pass in the word mumbai, and you get MUMBAI. Take SUM(salary). You pass in the salary column, and you get the total. You don&#8217;t have to know what happens inside. You just call the function and use what comes back.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">My simple way of looking at it: a calculator has buttons, and each button does one job. SQL functions are those buttons. Press the right one, and you save a lot of time. Every function has four things.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Part<\/strong><\/td><td><strong>Example<\/strong><\/td><td><strong>What it is<\/strong><\/td><\/tr><tr><td><strong>Name<\/strong><\/td><td>ROUND<\/td><td>the job it does<\/td><\/tr><tr><td><strong>Argument<\/strong><\/td><td>salary, 2<\/td><td>the input you give<\/td><\/tr><tr><td><strong>Return value<\/strong><\/td><td>54321.57<\/td><td>the answer you get<\/td><\/tr><tr><td><strong>Place of use<\/strong><\/td><td>SELECT, WHERE, HAVING<\/td><td>where you can write it<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Once you know these four, any new function you meet will look familiar.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Why learn them early? Because of what I&#8217;ve seen myself. Your queries get shorter. The answers are correct, since these functions are already tested. Other people can read your code easily, because AVG(salary) explains itself. And you can use the same function on any table, in any project. Also, you might have typed &#8220;windows functions in sql&#8221; in Google at some point. Lots of people do. The real name is window functions, and they have a full section below.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div id=\"imarticus-sql\" class=\"playground\">\n  <style>\n    #imarticus-sql {\n      font-family: Arial, sans-serif;\n      background: #f2f8f5;\n      color: #173b2c;\n      padding: 24px;\n      border-radius: 14px;\n      max-width: 100%;\n    }\n    #imarticus-sql * { box-sizing: border-box; }\n    #imarticus-sql h2 { color: #075c3b; margin-top: 0; }\n    #imarticus-sql .intro { color: #60776b; line-height: 1.6; }\n    #imarticus-sql .tabs {\n      display: flex; flex-wrap: wrap; gap: 9px; margin: 22px 0;\n    }\n    #imarticus-sql button {\n      cursor: pointer; border-radius: 7px; padding: 10px 15px;\n      font-size: 14px;\n    }\n    #imarticus-sql .tab {\n      background: white; color: #176344;\n      border: 1px solid #c9ded2;\n    }\n    #imarticus-sql .tab.active {\n      background: #008c55; color: white; border-color: #008c55;\n    }\n    #imarticus-sql .description {\n      background: white; padding: 16px;\n      border-left: 4px solid #009b62;\n      border-radius: 8px; margin-bottom: 18px;\n    }\n    #imarticus-sql .description h3 { margin: 0 0 8px; color: #087a4b; }\n    #imarticus-sql .description p { margin: 0; color: #61796b; line-height: 1.5; }\n    #imarticus-sql .section-title { font-weight: bold; margin: 16px 0 9px; }\n    #imarticus-sql .editor {\n      background: #102a20; border-radius: 9px; overflow: hidden;\n    }\n    #imarticus-sql .editor-head {\n      background: #173c2d; color: #d5f5e3;\n      padding: 12px 15px; font-size: 13px;\n    }\n    #imarticus-sql textarea {\n      display: block; width: 100%; min-height: 170px;\n      padding: 17px; resize: vertical; border: 0; outline: 0;\n      background: #102a20; color: #b9f5d0;\n      font: 14px\/1.7 Consolas, monospace;\n    }\n    #imarticus-sql .actions {\n      display: flex; flex-wrap: wrap; gap: 10px; margin-top: 13px;\n    }\n    #imarticus-sql .run {\n      background: #008c55; color: white; border: 0; font-weight: bold;\n    }\n    #imarticus-sql .reset {\n      background: white; color: #087a4b;\n      border: 1px solid #b9d8c6;\n    }\n    #imarticus-sql .preview {\n      background: white; border: 1px solid #d5e7dc;\n      border-radius: 9px; overflow: hidden; margin-top: 20px;\n    }\n    #imarticus-sql .preview-head {\n      background: #e4f3e9; color: #075c3b;\n      padding: 14px 16px; font-weight: bold;\n      display: flex; justify-content: space-between; gap: 10px;\n    }\n    #imarticus-sql .status { color: #008c55; font-size: 12px; }\n    #imarticus-sql .table-wrap { overflow-x: auto; }\n    #imarticus-sql table { width: 100%; border-collapse: collapse; font-size: 14px; text-align: left; }\n    #imarticus-sql th, #imarticus-sql td {\n      padding: 12px 15px; border-bottom: 1px solid #e1eee5; white-space: nowrap;\n    }\n    #imarticus-sql th { background: #edf7f0; color: #176344; }\n    #imarticus-sql td { color: #345746; }\n    #imarticus-sql .note { padding: 12px 16px; color: #60776b; font-size: 12px; line-height: 1.5; }\n    #imarticus-sql .tip {\n      background: #e2f3e8; color: #176344;\n      border-left: 4px solid #009b62;\n      border-radius: 7px; padding: 14px; margin-top: 18px;\n      font-size: 13px; line-height: 1.6;\n    }\n    #imarticus-sql .error { padding: 16px; color: #b42318; }\n    @media(max-width:550px) {\n      #imarticus-sql { padding: 15px; }\n      #imarticus-sql .tab { flex: 1 1 40%; }\n      #imarticus-sql h2 { font-size: 21px; }\n    }\n  <\/style>\n\n  <h2>SQL Functions Interactive Playground<\/h2>\n  <p class=\"intro\">Explore popular SQL functions, edit queries and understand how they transform data. Select a category to get started.<\/p>\n\n  <div class=\"tabs\">\n    <button class=\"tab active\" data-key=\"aggregate\">Aggregate<\/button>\n    <button class=\"tab\" data-key=\"string\">String<\/button>\n    <button class=\"tab\" data-key=\"numeric\">Numeric<\/button>\n    <button class=\"tab\" data-key=\"date\">Date<\/button>\n    <button class=\"tab\" data-key=\"window\">Window<\/button>\n  <\/div>\n\n  <div class=\"description\">\n    <h3 id=\"sql-title\">Aggregate Functions<\/h3>\n    <p id=\"sql-description\">Calculate totals, averages and counts from multiple rows of data.<\/p>\n  <\/div>\n\n  <div class=\"section-title\">SQL Code Editor<\/div>\n  <div class=\"editor\">\n    <div class=\"editor-head\">SQL Query \u00b7 Editable<\/div>\n    <textarea id=\"sql-code\" spellcheck=\"false\" aria-label=\"SQL code editor\">SELECT\n    department,\n    COUNT(*) AS total_employees,\n    ROUND(AVG(salary), 2) AS average_salary\nFROM employees\nGROUP BY department;<\/textarea>\n  <\/div>\n\n  <div class=\"actions\">\n    <button class=\"run\" id=\"sql-run\">\u25b6 Run Query<\/button>\n    <button class=\"reset\" id=\"sql-reset\">Reset Example<\/button>\n  <\/div>\n\n  <div class=\"preview\">\n    <div class=\"preview-head\">\n      <span>Query Result Preview<\/span>\n      <span class=\"status\" id=\"sql-status\">\u25cf Sample Result<\/span>\n    <\/div>\n    <div class=\"table-wrap\" id=\"sql-result\">\n      <table>\n        <thead><tr><th>Department<\/th><th>Employees<\/th><th>Average Salary<\/th><\/tr><\/thead>\n        <tbody>\n          <tr><td>Marketing<\/td><td>3<\/td><td>45000.00<\/td><\/tr>\n          <tr><td>Finance<\/td><td>2<\/td><td>55000.00<\/td><\/tr>\n          <tr><td>IT<\/td><td>3<\/td><td>65000.00<\/td><\/tr>\n        <\/tbody>\n      <\/table>\n    <\/div>\n    <div class=\"note\" id=\"sql-note\">Illustrative output using sample employee data. No live database connection.<\/div>\n  <\/div>\n\n  <div class=\"tip\" id=\"sql-tip\">\n    <strong>Learning tip:<\/strong> COUNT(*) counts all rows, while AVG(salary) calculates the average salary of each department.\n  <\/div>\n\n  <script>\n  (() => {\n    const root = document.getElementById(\"imarticus-sql\");\n    if (root.dataset.ready) return;\n    root.dataset.ready = \"yes\";\n\n    const data = {\n      aggregate: {\n        title: \"Aggregate Functions\",\n        desc: \"Calculate totals, averages and counts from multiple rows of data.\",\n        code: \"SELECT\\\\n    department,\\\\n    COUNT(*) AS total_employees,\\\\n    ROUND(AVG(salary), 2) AS average_salary\\\\nFROM employees\\\\nGROUP BY department;\",\n        headers: [\"Department\", \"Employees\", \"Average Salary\"],\n        rows: [[\"Marketing\",\"3\",\"45000.00\"],[\"Finance\",\"2\",\"55000.00\"],[\"IT\",\"3\",\"65000.00\"]],\n        tip: \"COUNT(*) counts every row. AVG(salary) calculates the average salary in each group. GROUP BY creates department groups.\"\n      },\n      string: {\n        title: \"String Functions\",\n        desc: \"Clean, format and combine text values using string functions.\",\n        code: \"SELECT\\\\n    UPPER(first_name) AS name_uppercase,\\\\n    LOWER(email) AS email_lowercase,\\\\n    TRIM(city) AS clean_city\\\\nFROM employees;\",\n        headers: [\"Uppercase Name\", \"Lowercase Email\", \"Clean City\"],\n        rows: [[\"RIYA\",\"riya@example.com\",\"Mumbai\"],[\"ARJUN\",\"arjun@example.com\",\"Delhi\"],[\"NEHA\",\"neha@example.com\",\"Pune\"]],\n        tip: \"UPPER() converts text to uppercase, LOWER() converts it to lowercase, and TRIM() removes leading and trailing spaces.\"\n      },\n      numeric: {\n        title: \"Numeric Functions\",\n        desc: \"Perform mathematical operations, round values and work with numbers.\",\n        code: \"SELECT\\\\n    order_id,\\\\n    amount,\\\\n    ROUND(amount * 1.18, 2) AS amount_with_tax\\\\nFROM orders;\",\n        headers: [\"Order ID\", \"Amount\", \"Amount With Tax\"],\n        rows: [[\"101\",\"1000.00\",\"1180.00\"],[\"102\",\"2500.50\",\"2950.59\"],[\"103\",\"500.00\",\"590.00\"]],\n        tip: \"ROUND() controls decimal places. The example calculates an amount including an assumed 18% tax.\"\n      },\n      date: {\n        title: \"Date Functions\",\n        desc: \"Extract years and months from dates to organise records and build reports.\",\n        code: \"SELECT\\\\n    order_id,\\\\n    order_date,\\\\n    EXTRACT(YEAR FROM order_date) AS order_year,\\\\n    EXTRACT(MONTH FROM order_date) AS order_month\\\\nFROM orders;\",\n        headers: [\"Order ID\", \"Order Date\", \"Year\", \"Month\"],\n        rows: [[\"101\",\"2026-01-15\",\"2026\",\"1\"],[\"102\",\"2026-02-10\",\"2026\",\"2\"],[\"103\",\"2026-03-22\",\"2026\",\"3\"]],\n        tip: \"EXTRACT() retrieves a specific part of a date, such as its year or month.\"\n      },\n      window: {\n        title: \"Window Functions\",\n        desc: \"Calculate rankings and compare related rows without removing individual records.\",\n        code: \"SELECT\\\\n    first_name,\\\\n    department,\\\\n    salary,\\\\n    RANK() OVER (\\\\n        PARTITION BY department\\\\n        ORDER BY salary DESC\\\\n    ) AS salary_rank\\\\nFROM employees;\",\n        headers: [\"Employee\", \"Department\", \"Salary\", \"Rank\"],\n        rows: [[\"Riya\",\"IT\",\"80000\",\"1\"],[\"Arjun\",\"IT\",\"70000\",\"2\"],[\"Neha\",\"IT\",\"60000\",\"3\"],[\"Aman\",\"Finance\",\"65000\",\"1\"],[\"Sara\",\"Finance\",\"45000\",\"2\"]],\n        tip: \"PARTITION BY creates separate rankings for each department. ORDER BY salary DESC ranks the highest salary first.\"\n      }\n    };\n\n    const $ = id => root.querySelector(\"#\" + id);\n    let selected = \"aggregate\";\n\n    function renderTable(example) {\n      const table = document.createElement(\"table\");\n      const thead = document.createElement(\"thead\");\n      const headRow = document.createElement(\"tr\");\n      example.headers.forEach(value => {\n        const th = document.createElement(\"th\");\n        th.textContent = value;\n        headRow.appendChild(th);\n      });\n      thead.appendChild(headRow);\n      const tbody = document.createElement(\"tbody\");\n      example.rows.forEach(row => {\n        const tr = document.createElement(\"tr\");\n        row.forEach(value => {\n          const td = document.createElement(\"td\");\n          td.textContent = value;\n          tr.appendChild(td);\n        });\n        tbody.appendChild(tr);\n      });\n      table.append(thead, tbody);\n      $(\"sql-result\").replaceChildren(table);\n    }\n\n    function load(key) {\n      selected = key;\n      const example = data[key];\n      $(\"sql-title\").textContent = example.title;\n      $(\"sql-description\").textContent = example.desc;\n      $(\"sql-code\").value = example.code.replace(\/\\\\n\/g, \"\\n\");\n      $(\"sql-tip\").innerHTML = \"\";\n      const strong = document.createElement(\"strong\");\n      strong.textContent = \"Learning tip: \";\n      $(\"sql-tip\").append(strong, document.createTextNode(example.tip));\n      $(\"sql-status\").textContent = \"\u25cf Sample Result\";\n      $(\"sql-note\").textContent = \"Illustrative output using sample data. No live database connection.\";\n      renderTable(example);\n      root.querySelectorAll(\".tab\").forEach(btn => {\n        btn.classList.toggle(\"active\", btn.dataset.key === key);\n      });\n    }\n\n    root.querySelectorAll(\".tab\").forEach(btn => {\n      btn.addEventListener(\"click\", () => load(btn.dataset.key));\n    });\n\n    $(\"sql-run\").addEventListener(\"click\", () => {\n      if (!$(\"sql-code\").value.trim()) {\n        $(\"sql-result\").textContent = \"Please enter a SQL query first.\";\n        $(\"sql-result\").className = \"error\";\n        $(\"sql-status\").textContent = \"\u25cf Empty Query\";\n        return;\n      }\n      $(\"sql-result\").className = \"table-wrap\";\n      renderTable(data[selected]);\n      $(\"sql-status\").textContent = \"\u25cf Illustrative Preview\";\n      $(\"sql-note\").textContent = \"The result is the sample output for this category. Your edited SQL is not executed. Connect a database engine to run custom queries.\";\n    });\n\n    $(\"sql-reset\").addEventListener(\"click\", () => load(selected));\n    load(\"aggregate\");\n  })();\n  <\/script>\n<\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Types of SQL Functions&nbsp;<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If you look at all the SQL functions, the list is very long. But it&#8217;s not hard once you group them. Here&#8217;s the simple grouping I use, and the rest of the blog follows it.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The main thing to check is what happens to the rows. Do you get fewer rows? The same number? Or the same rows plus one extra column? Your answer tells you which type to use.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Type<\/strong><\/td><td><strong>What it does<\/strong><\/td><td><strong>Rows<\/strong><\/td><td><strong>Examples<\/strong><\/td><\/tr><tr><td><strong>Aggregate<\/strong><\/td><td>sums up many rows<\/td><td>many in, one out<\/td><td>COUNT, SUM, AVG<\/td><\/tr><tr><td><strong>Single row<\/strong><\/td><td>works on one value<\/td><td>one in, one out<\/td><td>ROUND, ABS, CAST<\/td><\/tr><tr><td><strong>String<\/strong><\/td><td>works on text<\/td><td>one in, one out<\/td><td>TRIM, CONCAT<\/td><\/tr><tr><td><strong>Date<\/strong><\/td><td>works on dates<\/td><td>one in, one out<\/td><td>EXTRACT, CURRENT_DATE<\/td><\/tr><tr><td><strong>Window<\/strong><\/td><td>calculates across rows<\/td><td>all rows stay<\/td><td>RANK, LAG<\/td><\/tr><tr><td><strong>User defined<\/strong><\/td><td>made by you<\/td><td>depends<\/td><td>your own bonus rule<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Save this table somewhere. Whenever you&#8217;re stuck on a query, come back and ask yourself the rows question.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Small note. Function names change a little between database systems, so sometimes you need to check the exact name. And you can put one function inside another, like UPPER(TRIM(city)). The inside one runs first.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"683\" src=\"https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/types-of-sql-functions-1024x683.webp\" alt=\"sql functions types\n\" class=\"wp-image-278758\" srcset=\"https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/types-of-sql-functions-1024x683.webp 1024w, https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/types-of-sql-functions-300x200.webp 300w, https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/types-of-sql-functions-768x512.webp 768w, https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/types-of-sql-functions.webp 1536w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-background wp-block-paragraph\" style=\"background-color:#7bdbb426\"><strong>Also Read:<\/strong> <a href=\"https:\/\/imarticus.org\/blog\/python-the-beginners-guide-to-learning-a-powerful-programming-language\/\"><strong>Understanding Python&nbsp;<\/strong><\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>SQL Functions for Aggregation<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Most beginners start here, and I did too. In this part, I&#8217;ll tell you what aggregate SQL functions are, how they treat blank values, and how GROUP BY and HAVING work with them.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">What are aggregate functions in SQL? They take a group of rows and give back one value. Say you have 500 orders. COUNT(*) tells you there are 500. SUM(amount) tells you the total money. AVG(amount) tells you what a normal order looks like. Any time someone asks &#8220;how many&#8221; or &#8220;how much&#8221;, SQL aggregate functions are what you use.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Function<\/strong><\/td><td><strong>Gives you<\/strong><\/td><td><strong>Example<\/strong><\/td><\/tr><tr><td><strong>COUNT<\/strong><\/td><td>how many rows<\/td><td>COUNT(*)<\/td><\/tr><tr><td><strong>SUM<\/strong><\/td><td>the total<\/td><td>SUM(amount)<\/td><\/tr><tr><td><strong>AVG<\/strong><\/td><td>the average<\/td><td>AVG(salary)<\/td><\/tr><tr><td><strong>MIN<\/strong><\/td><td>the smallest<\/td><td>MIN(join_date)<\/td><\/tr><tr><td><strong>MAX<\/strong><\/td><td>the biggest<\/td><td>MAX(salary)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">These five cover maybe 80% of what you&#8217;ll write at work, so get them in your hand first. Some things that help with aggregate functions in SQL queries:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Blank (NULL) values are ignored by SUM, AVG, MIN, and MAX.<\/li>\n\n\n\n<li>COUNT(*) counts all rows. COUNT(column) counts only the ones that have a value. COUNT(DISTINCT column) counts each value one time.<\/li>\n\n\n\n<li>GROUP BY makes groups, like one per department.<\/li>\n\n\n\n<li>HAVING filters those groups. WHERE filters the rows before the grouping happens. Here&#8217;s an example from an employee table.<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code><\/code><\/pre>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">SELECT<\/span> department,\n       <span style=\"color:#c4b5fd !important;\">COUNT<\/span>(*) <span style=\"color:#60a5fa !important;\">AS<\/span> headcount,\n       <span style=\"color:#c4b5fd !important;\">ROUND<\/span>(<span style=\"color:#c4b5fd !important;\">AVG<\/span>(salary), <span style=\"color:#fbbf24 !important;\">2<\/span>) <span style=\"color:#60a5fa !important;\">AS<\/span> avg_salary\n<span style=\"color:#60a5fa !important;\">FROM<\/span> employees\n<span style=\"color:#60a5fa !important;\">GROUP BY<\/span> department\n<span style=\"color:#60a5fa !important;\">HAVING<\/span> <span style=\"color:#c4b5fd !important;\">COUNT<\/span>(*) &gt;= <span style=\"color:#fbbf24 !important;\">3<\/span>\n<span style=\"color:#60a5fa !important;\">ORDER BY<\/span> avg_salary <span style=\"color:#60a5fa !important;\">DESC<\/span>;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">So you get one line for each department that has at least 3 people, and the highest-paying team comes first. Easy to read, and a manager gets the answer in two seconds.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-background wp-block-paragraph\" style=\"background-color:#7bdbb426\"><strong><em>Did You Know?<\/em><\/strong> <em><\/em><em><br><\/em><em>Most analysts write an aggregate query on their first day at work. One SUM can replace thousands of rows with a single number. (<\/em><a href=\"https:\/\/www.postgresql.org\/docs\/18\/tutorial-agg.html\"><em>Source<\/em><\/a><em>)<\/em><\/p>\n\n\n\n<hr class=\"wp-block-separator has-text-color has-alpha-channel-opacity has-background\" style=\"background-color:#7bdbb426;color:#7bdbb426\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Single Row Functions in SQL<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Aggregates make rows fewer. Single-row functions do the opposite job: they fix each row, one at a time. I&#8217;ll go through numeric, conversion, and CASE here.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Single-row SQL functions take one value and give one value back. If your table has 1,000 rows, you get 1,000 answers. I mostly use them to clean a column or do a small calculation before the main query.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Group<\/strong><\/td><td><strong>Functions<\/strong><\/td><td><strong>Use<\/strong><\/td><\/tr><tr><td><strong>Numeric<\/strong><\/td><td>ROUND, CEIL, FLOOR, ABS, MOD<\/td><td>round prices, find remainder<\/td><\/tr><tr><td><strong>Conversion<\/strong><\/td><td>CAST<\/td><td>change text to number or date<\/td><\/tr><tr><td><strong>Blank handling<\/strong><\/td><td>COALESCE, NULLIF<\/td><td>fill blanks, avoid divide by zero<\/td><\/tr><tr><td><strong>Conditional<\/strong><\/td><td>CASE<\/td><td>make labels like High or Regular<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Nothing fancy in this table, but you&#8217;ll use these all the time.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">An example:<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">SELECT<\/span> order_id,\n       <span style=\"color:#c4b5fd !important;\">ROUND<\/span>(amount * <span style=\"color:#fbbf24 !important;\">1.18<\/span>, <span style=\"color:#fbbf24 !important;\">2<\/span>) <span style=\"color:#60a5fa !important;\">AS<\/span> amount_with_tax,\n       <span style=\"color:#c4b5fd !important;\">COALESCE<\/span>(discount, <span style=\"color:#fbbf24 !important;\">0<\/span>) <span style=\"color:#60a5fa !important;\">AS<\/span> discount_applied,\n       <span style=\"color:#60a5fa !important;\">CASE<\/span>\n         <span style=\"color:#60a5fa !important;\">WHEN<\/span> amount &gt;= <span style=\"color:#fbbf24 !important;\">5000<\/span> <span style=\"color:#60a5fa !important;\">THEN<\/span> <span style=\"color:#86efac !important;\">'High value'<\/span>\n         <span style=\"color:#60a5fa !important;\">ELSE<\/span> <span style=\"color:#86efac !important;\">'Regular'<\/span>\n       <span style=\"color:#60a5fa !important;\">END<\/span> <span style=\"color:#60a5fa !important;\">AS<\/span> order_band\n<span style=\"color:#60a5fa !important;\">FROM<\/span> orders;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">This adds tax, puts 0 where the discount is empty, and labels each order. COALESCE gives you the first value that isn&#8217;t blank, so your report never shows an empty box. And NULLIF changes a chosen value to NULL, which is a neat way to stop a divide-by-zero error.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That&#8217;s all for single-row functions in SQL. Small things, but your data looks a lot cleaner after them.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-background wp-block-paragraph\" style=\"background-color:#7bdbb426\"><strong>Also Read:<\/strong> <a href=\"https:\/\/imarticus.org\/blog\/python-for-data-science\/\"><strong>Python for Data Science<\/strong><\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>SQL Functions for Text Handling<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Real data is never clean. Names come in capital letters, emails have extra spaces, one city is written three ways. This part is about SQL functions and how they fix all that.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">String functions in SQL work on text. They can change the case, remove spaces, join two pieces, cut a part out, or replace a word. If you&#8217;ve ever fixed a long list of names by hand, you&#8217;ll like these.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Function<\/strong><\/td><td><strong>Does<\/strong><\/td><td><strong>Example<\/strong><\/td><td><strong>Result<\/strong><\/td><\/tr><tr><td><strong>UPPER \/ LOWER<\/strong><\/td><td>changes case<\/td><td>UPPER(&#8216;mumbai&#8217;)<\/td><td>MUMBAI<\/td><\/tr><tr><td><strong>TRIM<\/strong><\/td><td>removes extra spaces<\/td><td>TRIM(&#8216; data &#8216;)<\/td><td>data<\/td><\/tr><tr><td><strong>LENGTH<\/strong><\/td><td>counts letters (called LEN in some systems)<\/td><td>LENGTH(&#8216;SQL&#8217;)<\/td><td>3<\/td><\/tr><tr><td><strong>SUBSTRING<\/strong><\/td><td>takes a part<\/td><td>SUBSTRING(&#8216;analytics&#8217;, 1, 4)<\/td><td>anal<\/td><\/tr><tr><td><strong>CONCAT<\/strong><\/td><td>joins<\/td><td>CONCAT(&#8216;Data&#8217;, &#8216; &#8216;, &#8216;Science&#8217;)<\/td><td>Data Science<\/td><\/tr><tr><td><strong>REPLACE<\/strong><\/td><td>swaps text<\/td><td>REPLACE(&#8216;Q1-2026&#8217;, &#8216;-&#8216;, &#8216; &#8216;)<\/td><td>Q1 2026<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Every function does one small job. You get more power when you use two or three together.<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">SELECT<\/span> <span style=\"color:#c4b5fd !important;\">CONCAT<\/span>(\n         <span style=\"color:#c4b5fd !important;\">UPPER<\/span>(<span style=\"color:#c4b5fd !important;\">SUBSTRING<\/span>(first_name, <span style=\"color:#fbbf24 !important;\">1<\/span>, <span style=\"color:#fbbf24 !important;\">1<\/span>)),\n         <span style=\"color:#c4b5fd !important;\">LOWER<\/span>(<span style=\"color:#c4b5fd !important;\">SUBSTRING<\/span>(first_name, <span style=\"color:#fbbf24 !important;\">2<\/span>, <span style=\"color:#c4b5fd !important;\">LENGTH<\/span>(first_name))),\n         <span style=\"color:#86efac !important;\">' '<\/span>,\n         <span style=\"color:#c4b5fd !important;\">UPPER<\/span>(last_name)\n       ) <span style=\"color:#60a5fa !important;\">AS<\/span> full_name\n<span style=\"color:#60a5fa !important;\">FROM<\/span> employees;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">I use string SQL functions mostly for two things. One is cleaning email lists. The other is making city names uniform, so Mumbai, MUMBAI, and Mumbai count as the same place. Try it on your own contacts list. It&#8217;s much easier to learn when the data is something you know.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>SQL Functions for Dates and Time<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Think about the questions a boss asks. Sales this month? How many joined this year? How long has this invoice been pending? All of them are about dates, so you need date functions in SQL.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Date functions in SQL help you get today&#8217;s date, take out the year or month, add days and find the gap between two dates. Monthly reports and tenure calculations both run on them.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Task<\/strong><\/td><td><strong>Function<\/strong><\/td><td><strong>You get<\/strong><\/td><\/tr><tr><td><strong>Today&#8217;s date<\/strong><\/td><td>CURRENT_DATE<\/td><td>current date<\/td><\/tr><tr><td><strong>Date and time now<\/strong><\/td><td>NOW<\/td><td>date plus time<\/td><\/tr><tr><td><strong>Take a part out<\/strong><\/td><td>EXTRACT(YEAR FROM date)<\/td><td>year, month or day<\/td><\/tr><tr><td><strong>Add days<\/strong><\/td><td>DATE_ADD or DATEADD<\/td><td>a new date<\/td><\/tr><tr><td><strong>Gap between dates<\/strong><\/td><td>DATEDIFF<\/td><td>days or months<\/td><\/tr><tr><td><strong>Group by month<\/strong><\/td><td>DATE_TRUNC<\/td><td>first day of the month<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The names are a bit different in each database, but they all do the same thing. Learn the idea first and look up the spelling later.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here is a monthly sales query.<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">SELECT<\/span> <span style=\"color:#c4b5fd !important;\">EXTRACT<\/span>(<span style=\"color:#60a5fa !important;\">YEAR FROM<\/span> order_date) <span style=\"color:#60a5fa !important;\">AS<\/span> order_year,\n       <span style=\"color:#c4b5fd !important;\">EXTRACT<\/span>(<span style=\"color:#60a5fa !important;\">MONTH FROM<\/span> order_date) <span style=\"color:#60a5fa !important;\">AS<\/span> order_month,\n       <span style=\"color:#c4b5fd !important;\">SUM<\/span>(amount) <span style=\"color:#60a5fa !important;\">AS<\/span> monthly_sales\n<span style=\"color:#60a5fa !important;\">FROM<\/span> orders\n<span style=\"color:#60a5fa !important;\">GROUP BY<\/span> <span style=\"color:#c4b5fd !important;\">EXTRACT<\/span>(<span style=\"color:#60a5fa !important;\">YEAR FROM<\/span> order_date),\n         <span style=\"color:#c4b5fd !important;\">EXTRACT<\/span>(<span style=\"color:#60a5fa !important;\">MONTH FROM<\/span> order_date)\n<span style=\"color:#60a5fa !important;\">ORDER BY<\/span> order_year, order_month;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">See, the date function makes the month groups, and SUM fills each one with the total. Lots of dashboards are built on just this.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Want to practise date functions in SQL? Find how many years each employee has been in the company. Or find which day of the week gets the most orders. Or compare this January with last January.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-background wp-block-paragraph\" style=\"background-color:#7bdbb426\"><strong>Also Read:<\/strong> <a href=\"https:\/\/imarticus.org\/blog\/data-analytics-courses-7-skills-everyone-needs-to-know\/\"><strong>Data Analytics Skills<\/strong><\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>SQL Window Functions in Action<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Okay, this is the part everyone is waiting for. Interviewers ask about it a lot. Below I&#8217;ll explain what window functions in SQL are, how to write them, and how ranking, LAG, LEAD, and running totals work.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What Are Window Functions in SQL<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s the simple answer: window functions in SQL are functions in SQL. A window function does a calculation for each row by looking at some other related rows. The group of rows it looks at is called the window. And the best part is that your rows don&#8217;t disappear.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">With a normal aggregate, you get one row per group. With SQL window functions, you keep all the rows and get a new column. So you can show one employee&#8217;s salary and their department&#8217;s average next to each other. Or rank products inside each category. Or compare today&#8217;s sales with yesterday&#8217;s, without joining the table to itself. That&#8217;s why window functions SQL questions come up so often.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Window Functions SQL Syntax<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The pattern is always the same.<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#c4b5fd !important;\">function_name<\/span>(column) <span style=\"color:#60a5fa !important;\">OVER<\/span> (\n    <span style=\"color:#60a5fa !important;\">PARTITION BY<\/span> group_column\n    <span style=\"color:#60a5fa !important;\">ORDER BY<\/span> sort_column\n)<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>OVER<\/strong> says this is a window function.<\/li>\n\n\n\n<li><strong>PARTITION BY<\/strong> splits rows into groups, like one per department. You can skip it, and then the whole table is one group.<\/li>\n\n\n\n<li><strong>ORDER BY<\/strong> sets the order inside each group.<\/li>\n\n\n\n<li>A <strong>frame<\/strong> (ROWS BETWEEN) can tell it exactly how many rows to include, like all rows up to the current one.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Ranking With Window Functions<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Say three people earn 90, 90, and 80. See what each ranking function does.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Salary<\/strong><\/td><td><strong>ROW_NUMBER<\/strong><\/td><td><strong>RANK<\/strong><\/td><td><strong>DENSE_RANK<\/strong><\/td><\/tr><tr><td><strong>90<\/strong><\/td><td>1<\/td><td>1<\/td><td>1<\/td><\/tr><tr><td><strong>90<\/strong><\/td><td>2<\/td><td>1<\/td><td>1<\/td><\/tr><tr><td><strong>80<\/strong><\/td><td>3<\/td><td>3<\/td><td>2<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">ROW_NUMBER never repeats a number. RANK gives the same number to a tie and then jumps ahead. DENSE_RANK gives the same number to a tie and doesn&#8217;t jump. Choose the one that matches how you want ties to look.<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">SELECT<\/span> department, first_name, salary,\n       <span style=\"color:#c4b5fd !important;\">DENSE_RANK<\/span>() <span style=\"color:#60a5fa !important;\">OVER<\/span> (\n           <span style=\"color:#60a5fa !important;\">PARTITION BY<\/span> department\n           <span style=\"color:#60a5fa !important;\">ORDER BY<\/span> salary <span style=\"color:#60a5fa !important;\">DESC<\/span>\n       ) <span style=\"color:#60a5fa !important;\">AS<\/span> salary_rank\n<span style=\"color:#60a5fa !important;\">FROM<\/span> employees;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">Now every department has its own ranking. Put this in a CTE, filter salary_rank = 1, and you get the top earner of each team.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Comparing Rows With LAG and LEAD<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">LAG brings the value from the row before. LEAD brings the value from the next row. Good for month-on-month change.<\/p>\n\n\n\n<div class=\"sql-code-card\">\n  <div class=\"sql-code-header\">\n    <span class=\"sql-language\">SQL<\/span>\n    <button class=\"sql-copy-btn\" onclick=\"copySQLCode(this)\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre><code><span class=\"sql-keyword\">SELECT<\/span> order_month, revenue,\n       <span class=\"sql-function\">LAG<\/span>(revenue) <span class=\"sql-keyword\">OVER<\/span> (\n           <span class=\"sql-keyword\">ORDER BY<\/span> order_month\n       ) <span class=\"sql-keyword\">AS<\/span> previous_month\n<span class=\"sql-keyword\">FROM<\/span> monthly_revenue;<\/code><\/pre>\n<\/div>\n\n<style>\n.sql-code-card {\n  background: #111827;\n  border-radius: 12px;\n  overflow: hidden;\n  margin: 20px 0;\n  border: 1px solid #263449;\n  font-family: Consolas, Monaco, monospace;\n}\n\n.sql-code-header {\n  display: flex;\n  justify-content: space-between;\n  align-items: center;\n  padding: 12px 18px;\n  background: #1f2937;\n  border-bottom: 1px solid #374151;\n}\n\n.sql-language {\n  color: #d1d5db;\n  font-size: 13px;\n  font-weight: 600;\n  letter-spacing: 1px;\n}\n\n.sql-copy-btn {\n  background: #286946;\n  color: #fff;\n  border: none;\n  border-radius: 5px;\n  padding: 7px 13px;\n  font-size: 12px;\n  cursor: pointer;\n}\n\n.sql-copy-btn:hover {\n  background: #1f5237;\n}\n\n.sql-code-card pre {\n  margin: 0;\n  padding: 20px;\n  overflow-x: auto;\n  color: #e5e7eb;\n  font-size: 14px;\n  line-height: 1.8;\n  white-space: pre;\n}\n\n.sql-keyword {\n  color: #60a5fa;\n  font-weight: 600;\n}\n\n.sql-function {\n  color: #c4b5fd;\n}\n\n@media (max-width: 600px) {\n  .sql-code-card pre {\n    font-size: 12px;\n    padding: 14px;\n  }\n}\n<\/style>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card')\n    .querySelector('code').innerText;\n\n  navigator.clipboard.writeText(code).then(() => {\n    button.innerText = 'Copied!';\n    setTimeout(() => {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  });\n}\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Running Totals and Moving Averages<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When you add ORDER BY inside OVER on a SUM, you get a running total.<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">SELECT<\/span> order_date, amount,\n       <span style=\"color:#c4b5fd !important;\">SUM<\/span>(amount) <span style=\"color:#60a5fa !important;\">OVER<\/span> (\n           <span style=\"color:#60a5fa !important;\">ORDER BY<\/span> order_date\n           <span style=\"color:#60a5fa !important;\">ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<\/span>\n       ) <span style=\"color:#60a5fa !important;\">AS<\/span> running_total\n<span style=\"color:#60a5fa !important;\">FROM<\/span> daily_sales;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">Change the frame to ROWS BETWEEN 6 PRECEDING AND CURRENT ROW and use AVG, and it becomes a 7-row moving average. Finance and sales teams use these every day.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>If you need<\/strong><\/td><td><strong>Use<\/strong><\/td><\/tr><tr><td><strong>top N in each group<\/strong><\/td><td>ROW_NUMBER, RANK, DENSE_RANK<\/td><\/tr><tr><td><strong>previous or next value<\/strong><\/td><td>LAG, LEAD<\/td><\/tr><tr><td><strong>running total<\/strong><\/td><td>SUM with OVER<\/td><\/tr><tr><td><strong>moving average<\/strong><\/td><td>AVG with a frame<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">My advice: practise each line of this table on some data of your own. Interviewers love asking these, and not many freshers have tried them.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-background wp-block-paragraph\" style=\"background-color:#7bdbb426\"><strong>Also Read:<\/strong> <a href=\"https:\/\/imarticus.org\/blog\/sql-for-data-visualization\/\"><strong>SQL for Data visualisation<\/strong><\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Analytical Functions in SQL<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You will also hear about analytical functions in SQL. They are close cousins of window functions. In this part, I&#8217;ll show the ones worth knowing.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Many people use &#8220;analytic functions&#8221; and &#8220;window functions&#8221; to mean the same thing, and it&#8217;s okay, because they also use OVER. The difference is in the question they answer. They tell you where a row stands compared with the rest. Is it in the top group, the middle, or the bottom?<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Function<\/strong><\/td><td><strong>Tells you<\/strong><\/td><td><strong>Good for<\/strong><\/td><\/tr><tr><td><strong>NTILE(n)<\/strong><\/td><td>splits rows into n equal groups<\/td><td>quartiles, customer tiers<\/td><\/tr><tr><td><strong>PERCENT_RANK<\/strong><\/td><td>position from 0 to 1<\/td><td>percentile scores<\/td><\/tr><tr><td><strong>CUME_DIST<\/strong><\/td><td>share of rows up to this value<\/td><td>cumulative view<\/td><\/tr><tr><td><strong>FIRST_VALUE<\/strong><\/td><td>first value in the window<\/td><td>first purchase<\/td><\/tr><tr><td><strong>LAST_VALUE<\/strong><\/td><td>last value in the window<\/td><td>latest status<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">All of them keep your rows, and they add one more column of information. Teams use them a lot for customer groups and scorecards.<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">SELECT<\/span> customer_id, total_spend,\n       <span style=\"color:#c4b5fd !important;\">NTILE<\/span>(<span style=\"color:#fbbf24 !important;\">4<\/span>) <span style=\"color:#60a5fa !important;\">OVER<\/span> (<span style=\"color:#60a5fa !important;\">ORDER BY<\/span> total_spend <span style=\"color:#60a5fa !important;\">DESC<\/span>) <span style=\"color:#60a5fa !important;\">AS<\/span> spend_group\n<span style=\"color:#60a5fa !important;\">FROM<\/span> customer_summary;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">Group 1 is your biggest spenders, and group 4 is the smallest. Marketing can then send a different message to each group. One tip for LAST_VALUE: end the frame at UNBOUNDED FOLLOWING, and it will look at the whole window and give the value you expect.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>User-Defined Functions in SQL<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes a company has a rule that no built-in function knows, like how to calculate a bonus. For that, you make your own. I&#8217;ll explain user-defined functions in SQL here, the types, and when to use them.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">User-defined functions in SQL, or UDFs, are functions you write once and use again and again, just like built-in ones. So if the bonus is 10% of salary, you write that rule one time and every report can use it. There are two common types. A scalar UDF returns one value, like the bonus of one employee. A table-valued UDF returns a small table, and you can use it like a normal table in a query.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">A simple example. The syntax is a little different from one database to another, but the idea is the same.<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">CREATE FUNCTION<\/span> <span style=\"color:#c4b5fd !important;\">bonus_amount<\/span>(salary <span style=\"color:#f9a8d4 !important;\">DECIMAL<\/span>(<span style=\"color:#fbbf24 !important;\">10<\/span>,<span style=\"color:#fbbf24 !important;\">2<\/span>))\n<span style=\"color:#60a5fa !important;\">RETURNS<\/span> <span style=\"color:#f9a8d4 !important;\">DECIMAL<\/span>(<span style=\"color:#fbbf24 !important;\">10<\/span>,<span style=\"color:#fbbf24 !important;\">2<\/span>)\n<span style=\"color:#60a5fa !important;\">RETURN<\/span> salary * <span style=\"color:#fbbf24 !important;\">0.10<\/span>;\n\n<span style=\"color:#60a5fa !important;\">SELECT<\/span> first_name, salary, <span style=\"color:#c4b5fd !important;\">bonus_amount<\/span>(salary) <span style=\"color:#60a5fa !important;\">AS<\/span> bonus\n<span style=\"color:#60a5fa !important;\">FROM<\/span> employees;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">Now anyone in the team can call bonus_amount() and get the same answer. If the rule changes next year, you change it in one place and all the reports follow.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><\/td><td><strong>Built-in function<\/strong><\/td><td><strong>User-defined function<\/strong><\/td><\/tr><tr><td><strong>Who makes it<\/strong><\/td><td>the database<\/td><td>you or your team<\/td><\/tr><tr><td><strong>Good for<\/strong><\/td><td>totals, text cleaning, dates<\/td><td>your company&#8217;s own rules<\/td><\/tr><tr><td><strong>Changing it<\/strong><\/td><td>not needed<\/td><td>change once, works everywhere<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">What I do is try a built-in function first. If the same rule keeps coming back in many queries, then I make a UDF.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-background wp-block-paragraph\" style=\"background-color:#7bdbb426\"><strong>Also Read:<\/strong> <a href=\"https:\/\/imarticus.org\/blog\/master-sql-for-data-analytics-careers\/\"><strong>Master SQL for Data Analytics<\/strong><\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>SQL Functions and AI<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">These days, AI tools can write SQL for you. You type a question and get a query back. So why learn SQL functions at all? Because the AI writes the query, but you still have to check the answer. And you can only check it if you know what the functions do.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here\u2019s a small example. Say you ask an AI tool for the average discount on your orders. It gives you AVG(discount). The query runs, and the number looks fine. But AVG skips blank values. Every order with no discount gets left out, so your average comes out higher than it should be. If you know how NULL works, you spot this in a minute. If you don\u2019t, the wrong number goes into your report.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Where AI helps<\/strong><\/td><td><strong>Example<\/strong><\/td><td><strong>What you should check<\/strong><\/td><\/tr><tr><td><strong>First draft of a query<\/strong><\/td><td>\u201cRank employees by salary in each department\u201d<\/td><td>Did it use RANK or DENSE_RANK?<\/td><\/tr><tr><td><strong>Explaining a query<\/strong><\/td><td>Paste a long query and ask what it does<\/td><td>Does the explanation match the output?<\/td><\/tr><tr><td><strong>Fixing errors<\/strong><\/td><td>Paste the error message<\/td><td>Does the fix work in your database?<\/td><\/tr><tr><td><strong>Changing syntax<\/strong><\/td><td>MySQL to PostgreSQL<\/td><td>Are the function names right?<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">So AI saves you typing time. Knowing your functions saves you from wrong answers.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"683\" src=\"https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/sql-functions-and-ai-1024x683.webp\" alt=\"ai and sql functions\n\" class=\"wp-image-278759\" srcset=\"https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/sql-functions-and-ai-1024x683.webp 1024w, https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/sql-functions-and-ai-300x200.webp 300w, https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/sql-functions-and-ai-768x512.webp 768w, https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/sql-functions-and-ai.webp 1536w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\"><strong>AI Functions Inside SQL<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">There\u2019s one more link between the two. Some platforms now let you call an AI model inside a query, just like any other function. Snowflake is one example. Its SENTIMENT function reads a piece of text and gives back a score between -1 and 1.<\/p>\n\n\n\n<div class=\"sql-code-card\" style=\"background:#111827 !important; border:1px solid #475569; border-radius:12px; overflow:hidden; margin:20px 0; font-family:Consolas,Monaco,monospace;\">\n\n  <div style=\"display:flex; justify-content:space-between; align-items:center; padding:12px 18px; background:#1f2937 !important; border-bottom:1px solid #374151;\">\n    <span style=\"color:#ffffff !important; font-size:13px; font-weight:600; letter-spacing:1px;\">SQL<\/span>\n\n    <button type=\"button\" onclick=\"copySQLCode(this)\" style=\"background:#286946 !important; color:#ffffff !important; border:0; border-radius:5px; padding:7px 13px; font-size:12px; cursor:pointer;\">\n      Copy Code\n    <\/button>\n  <\/div>\n\n  <pre style=\"display:block; background:#111827 !important; color:#e5e7eb !important; padding:20px; margin:0; overflow-x:auto; white-space:pre; font-family:Consolas,Monaco,monospace; font-size:14px; line-height:1.9; opacity:1 !important;\"><code style=\"background:transparent !important; color:#e5e7eb !important; font-family:Consolas,Monaco,monospace;\"><span style=\"color:#60a5fa !important;\">SELECT<\/span> product_id,\n       <span style=\"color:#c4b5fd !important;\">ROUND<\/span>(<span style=\"color:#c4b5fd !important;\">AVG<\/span>(<span style=\"color:#c4b5fd !important;\">SNOWFLAKE.CORTEX.SENTIMENT<\/span>(review_text)), <span style=\"color:#fbbf24 !important;\">2<\/span>) <span style=\"color:#60a5fa !important;\">AS<\/span> avg_sentiment\n<span style=\"color:#60a5fa !important;\">FROM<\/span> product_reviews\n<span style=\"color:#60a5fa !important;\">GROUP BY<\/span> product_id;<\/code><\/pre>\n\n<\/div>\n\n<script>\nfunction copySQLCode(button) {\n  const code = button.closest('.sql-code-card').querySelector('pre code').innerText;\n\n  navigator.clipboard.writeText(code).then(function() {\n    button.innerText = 'Copied!';\n    setTimeout(function() {\n      button.innerText = 'Copy Code';\n    }, 2000);\n  }).catch(function() {\n    button.innerText = 'Copy failed';\n  });\n}\n<\/script>\n\n\n\n<p class=\"wp-block-paragraph\">Here, SENTIMENT scores every review. AVG turns those scores into one number per product. GROUP BY makes the product groups. Only one part is AI. The rest is the same SQL you learned above.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Function names change between platforms, so check your database\u2019s documentation before you try this. One habit I\u2019d suggest: when an AI tool gives you a query, run it on a small sample first. Check two or three rows by hand. It takes a minute and saves you a lot of trouble later.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Habits That Keep SQL Functions Fast<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">A query that works is good. A query that works fast is better. Here are a few habits I follow so my SQL functions run well even on big tables.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>Habit<\/strong><\/td><td><strong>Why it helps<\/strong><\/td><\/tr><tr><td><strong>Select only the columns you need<\/strong><\/td><td>Less data to read<\/td><\/tr><tr><td><strong>Use WHERE early<\/strong><\/td><td>Functions run on fewer rows<\/td><\/tr><tr><td><strong>Keep indexed columns plain in WHERE<\/strong><\/td><td>The database can use its index<\/td><\/tr><tr><td><strong>Use CTEs for long queries<\/strong><\/td><td>Easier to read and test<\/td><\/tr><tr><td><strong>Give clear names to columns<\/strong><\/td><td>Reports are easy to follow<\/td><\/tr><tr><td><strong>Test on a small sample first<\/strong><\/td><td>Quick feedback<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">None of these is difficult. Start doing them from your first week, and they become a habit by themselves.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Two more things from me. Keep a small notebook of the queries you like, for example, top N per group, because you&#8217;ll reuse them. And if a function looks new, ask an AI assistant to explain it, then run the query yourself to check.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Go Beyond SQL Functions with Imarticus<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You can read about SQL functions for weeks, but you only get good by doing real projects with someone guiding you. That&#8217;s what our programme gives you, and here&#8217;s how.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Our Postgraduate Program in Data Science and Analytics with GenAI has SQL in the core syllabus. You learn aggregation, joins, subqueries, window functions, CTEs, and AI-assisted query optimisation. There&#8217;s also a mini project on social media analysis. If you&#8217;re looking for a <a href=\"https:\/\/imarticus.org\/postgraduate-program-in-data-science-analytics\/\"><strong>data analytics course<\/strong><\/a> where SQL is taught for real jobs, this is the one I&#8217;d suggest.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><strong>What you get<\/strong><\/td><td><strong>Why it helps<\/strong><\/td><\/tr><tr><td><strong>6 months; on weekdays, weekend option also available<\/strong><\/td><td>Fits students and working people<\/td><\/tr><tr><td><strong>300+ hours of live online and classroom learning<\/strong><\/td><td>Doubts get solved quickly<\/td><\/tr><tr><td><strong>35+ tools and projects<\/strong><\/td><td>Practice with SQL, Python, Excel, Power BI, and more<\/td><\/tr><tr><td><strong>Data Science and AI Studio<\/strong><\/td><td>Hackathons, blogathons, and masterclasses<\/td><\/tr><tr><td><strong>Internships and project portfolio<\/strong><\/td><td>Proof of your skills for recruiters<\/td><\/tr><tr><td><strong>40+ hours of live job preparation<\/strong><\/td><td>Resume, interview, and soft skills, with mock interviews<\/td><\/tr><tr><td><strong>10 guaranteed interviews and 100% job assurance<\/strong><\/td><td>A clear path to a job<\/td><\/tr><tr><td><strong>NSDC certification<\/strong><\/td><td>A recognised certificate<\/td><\/tr><tr><td><strong>1400+ placements in FY2026, 52% average salary hike, 22.5 LPA highest salary<\/strong><\/td><td>Results from real learners<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">If you look at the list, every point is there to remove one worry. The curriculum teaches you, the studio gives you practice, the placement team connects you to companies, and a dedicated program manager with one-to-one mentorship stays with you. Live GenAI sessions are added on top, so you finish with the tools data teams use today.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-background wp-block-paragraph\" style=\"background-color:#7bdbb426\"><strong>Also Read:<\/strong> <a href=\"https:\/\/imarticus.org\/blog\/data-engineer-salary\/\"><strong><\/strong><strong>Data Engineer Salary<\/strong><\/a><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>FAQs About SQL Functions<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">These are the frequently asked questions people ask me the most. I have kept the answers very short and simple.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What are Aggregate Functions in SQL?\u00a0<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Aggregate functions in SQL are simple: suppose you have a bag of marbles and want to know how many are inside. Aggregate functions count or add things for a whole group together. COUNT, SUM, AVG, MIN, and MAX are the main ones.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What are Window Functions in SQL?<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Window functions in SQL are like this: think of a class photo. Everyone stands in their own place, and the teacher writes the rank next to each child&#8217;s name. Window functions do that. They add an answer next to every row and keep all the rows.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What is the Difference Between Aggregate and Window Functions?<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">An aggregate function gives one answer for the whole group, like one score for the whole class. A window function gives an answer for each child. So aggregate gives fewer rows, and window keeps all of them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What are Single-Row Functions in SQL?<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Single-row functions in SQL look at one row at a time. You give one word, you get one answer. UPPER, ROUND, and CAST are some examples.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What are String Functions in SQL?<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">String functions in SQL are for strings, which are just letters, like a name. String functions fix those letters, join them, or cut them. TRIM takes away extra spaces, and CONCAT joins two words.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What are Date Functions in SQL?<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Data functions in SQL work with days, months, and years. We can get today&#8217;s date, find the month from a birthday, or count the days between two dates. Like a calendar that can do sums.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What are Analytical Functions in SQL?<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">They are like window functions&#8217; cousins. They tell us where a row stands among the others, like who is in the top group. NTILE and LAG are two of them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>What are User-Defined Functions in SQL?<\/strong><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">User-defined functions in SQL: These are functions you make yourself. If you have your own rule, like a bonus calculation, you write it once and use it many times. Like making up a game rule and teaching everyone.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Where Your SQL Journey Goes Next<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SQL functions look like a big list in the beginning, but they are really only a few groups. Aggregate functions add things up. Single-row functions clean each value. String and date functions handle text and time. Window and analytical functions compare rows, and user-defined functions keep your own rules. When you see it like this, every new function feels like one you already know.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">My honest advice is to keep practice light. Take a small table, like your monthly expenses, and try each group once. Rank something. Make a running total. Clean a list of names. Count the days between two dates. Ten minutes every day is better than one long session in a month, and soon you will write queries without opening your notes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">And when you feel ready for the next step, our <a href=\"https:\/\/imarticus.org\/postgraduate-program-in-data-science-analytics\/\"><strong>data analytics course<\/strong><\/a> has live classes, real projects, mentors, and a placement team that is with you till you get placed. The best time to start is today, and I&#8217;d love to see where these skills take you.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Aggregate, window, string, date and custom SQL functions explained with simple, handy examples fast.<\/p>\n","protected":false},"author":1,"featured_media":278760,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"om_disable_all_campaigns":false,"_lmt_disableupdate":"","_lmt_disable":"","_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[5957],"tags":[],"class_list":["post-278755","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analytics"],"acf":{"ls_key":"PG - Analytics","youtube-url-id":"","publised_date":"","recommended_posts":""},"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.4 - aioseo.com -->\n\t<meta name=\"description\" content=\"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Imarticus Learning\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/imarticus.org\/blog\/sql-functions\/\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.4\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_GB\" \/>\n\t\t<meta property=\"og:site_name\" content=\"Imarticus Blog -\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"SQL Functions Every Analyst Uses: Aggregate to Window\" \/>\n\t\t<meta property=\"og:description\" content=\"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/imarticus.org\/blog\/sql-functions\/\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2026-11-03T10:16:01+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2026-11-03T10:16:05+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ImarticusLearning\/\" \/>\n\t\t<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n\t\t<meta name=\"twitter:site\" content=\"@imarticus\" \/>\n\t\t<meta name=\"twitter:title\" content=\"SQL Functions Every Analyst Uses: Aggregate to Window\" \/>\n\t\t<meta name=\"twitter:description\" content=\"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.\" \/>\n\t\t<meta name=\"twitter:creator\" content=\"@imarticus\" \/>\n\t\t<script type=\"application\/ld+json\" class=\"aioseo-schema\">\n\t\t\t{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"BlogPosting\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#blogposting\",\"name\":\"SQL Functions Every Analyst Uses: Aggregate to Window\",\"headline\":\"SQL Functions That Make Data Analysis Easier\",\"author\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/author\\\/imarticus\\\/#author\"},\"publisher\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/#organization\"},\"image\":{\"@type\":\"ImageObject\",\"url\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/11\\\/sql-functions.webp\",\"width\":1200,\"height\":630},\"datePublished\":\"2026-11-03T15:46:01+05:30\",\"dateModified\":\"2026-11-03T15:46:05+05:30\",\"inLanguage\":\"en-GB\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#webpage\"},\"isPartOf\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#webpage\"},\"articleSection\":\"Data Analytics\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#breadcrumblist\",\"itemListElement\":[{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/#listItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/\",\"nextItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/category\\\/data-analytics\\\/#listItem\",\"name\":\"Data Analytics\"}},{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/category\\\/data-analytics\\\/#listItem\",\"position\":2,\"name\":\"Data Analytics\",\"item\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/category\\\/data-analytics\\\/\",\"nextItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#listItem\",\"name\":\"SQL Functions That Make Data Analysis Easier\"},\"previousItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/#listItem\",\"name\":\"Home\"}},{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#listItem\",\"position\":3,\"name\":\"SQL Functions That Make Data Analysis Easier\",\"previousItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/category\\\/data-analytics\\\/#listItem\",\"name\":\"Data Analytics\"}}]},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/#organization\",\"name\":\"Imarticus Learning\",\"description\":\"Imarticus Learning is the leading institute for career-focused finance and tech programs. Looking for an investment banking course in Mumbai? Our Certified Investment Banking Operations Program (CIBOP) offers industry-leading placements with 100% Job Assurance, making it one of the most sought-after investment banking courses in Mumbai. Aspiring data professionals can enrol in our data science course in Mumbai, the Postgraduate Program in Data Science &amp; Analytics with GenAI (PGA), designed to deliver the highest salaries and a vast hiring network. As a top destination for data science training in Mumbai, we offer live training, hands-on projects &amp; guaranteed outcomes. Imarticus Learning, your launchpad for high-impact careers.\",\"url\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/\",\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/ImarticusLearning\\\/\",\"https:\\\/\\\/x.com\\\/imarticus\",\"https:\\\/\\\/www.instagram.com\\\/imarticus\\\/\",\"https:\\\/\\\/www.youtube.com\\\/@ImarticusLearningInstitute\",\"https:\\\/\\\/in.linkedin.com\\\/company\\\/imarticuslearning\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/author\\\/imarticus\\\/#author\",\"url\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/author\\\/imarticus\\\/\",\"name\":\"Imarticus Learning\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#authorImage\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/26c0ffd94601c4078ba4c61e5b9dd95dbb2872f64fd398e2c5d020e8ff0eb581?s=96&d=mm&r=g\",\"width\":96,\"height\":96,\"caption\":\"Imarticus Learning\"}},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#webpage\",\"url\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/\",\"name\":\"SQL Functions Every Analyst Uses: Aggregate to Window\",\"description\":\"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.\",\"inLanguage\":\"en-GB\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/#website\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#breadcrumblist\"},\"author\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/author\\\/imarticus\\\/#author\"},\"creator\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/author\\\/imarticus\\\/#author\"},\"image\":{\"@type\":\"ImageObject\",\"url\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/11\\\/sql-functions.webp\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#mainImage\",\"width\":1200,\"height\":630},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/sql-functions\\\/#mainImage\"},\"datePublished\":\"2026-11-03T15:46:01+05:30\",\"dateModified\":\"2026-11-03T15:46:05+05:30\"},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/\",\"name\":\"Imarticus Learning\",\"inLanguage\":\"en-GB\",\"publisher\":{\"@id\":\"https:\\\/\\\/imarticus.org\\\/blog\\\/#organization\"}}]}\n\t\t<\/script>\n\t\t<script type=\"text\/javascript\">\n\t\t\t(function(c,l,a,r,i,t,y){\n\t\t\tc[a]=c[a]||function(){(c[a].q=c[a].q||[]).push(arguments)};t=l.createElement(r);t.async=1;\n\t\t\tt.src=\"https:\/\/www.clarity.ms\/tag\/\"+i+\"?ref=aioseo\";y=l.getElementsByTagName(r)[0];y.parentNode.insertBefore(t,y);\n\t\t})(window, document, \"clarity\", \"script\", \"p9rn6xgm87\");\n\t\t<\/script>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"SQL Functions Every Analyst Uses: Aggregate to Window","description":"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.","canonical_url":"https:\/\/imarticus.org\/blog\/sql-functions\/","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"BlogPosting","@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#blogposting","name":"SQL Functions Every Analyst Uses: Aggregate to Window","headline":"SQL Functions That Make Data Analysis Easier","author":{"@id":"https:\/\/imarticus.org\/blog\/author\/imarticus\/#author"},"publisher":{"@id":"https:\/\/imarticus.org\/blog\/#organization"},"image":{"@type":"ImageObject","url":"https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/sql-functions.webp","width":1200,"height":630},"datePublished":"2026-11-03T15:46:01+05:30","dateModified":"2026-11-03T15:46:05+05:30","inLanguage":"en-GB","mainEntityOfPage":{"@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#webpage"},"isPartOf":{"@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#webpage"},"articleSection":"Data Analytics"},{"@type":"BreadcrumbList","@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#breadcrumblist","itemListElement":[{"@type":"ListItem","@id":"https:\/\/imarticus.org\/blog\/#listItem","position":1,"name":"Home","item":"https:\/\/imarticus.org\/blog\/","nextItem":{"@type":"ListItem","@id":"https:\/\/imarticus.org\/blog\/category\/data-analytics\/#listItem","name":"Data Analytics"}},{"@type":"ListItem","@id":"https:\/\/imarticus.org\/blog\/category\/data-analytics\/#listItem","position":2,"name":"Data Analytics","item":"https:\/\/imarticus.org\/blog\/category\/data-analytics\/","nextItem":{"@type":"ListItem","@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#listItem","name":"SQL Functions That Make Data Analysis Easier"},"previousItem":{"@type":"ListItem","@id":"https:\/\/imarticus.org\/blog\/#listItem","name":"Home"}},{"@type":"ListItem","@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#listItem","position":3,"name":"SQL Functions That Make Data Analysis Easier","previousItem":{"@type":"ListItem","@id":"https:\/\/imarticus.org\/blog\/category\/data-analytics\/#listItem","name":"Data Analytics"}}]},{"@type":"Organization","@id":"https:\/\/imarticus.org\/blog\/#organization","name":"Imarticus Learning","description":"Imarticus Learning is the leading institute for career-focused finance and tech programs. Looking for an investment banking course in Mumbai? Our Certified Investment Banking Operations Program (CIBOP) offers industry-leading placements with 100% Job Assurance, making it one of the most sought-after investment banking courses in Mumbai. Aspiring data professionals can enrol in our data science course in Mumbai, the Postgraduate Program in Data Science &amp; Analytics with GenAI (PGA), designed to deliver the highest salaries and a vast hiring network. As a top destination for data science training in Mumbai, we offer live training, hands-on projects &amp; guaranteed outcomes. Imarticus Learning, your launchpad for high-impact careers.","url":"https:\/\/imarticus.org\/blog\/","sameAs":["https:\/\/www.facebook.com\/ImarticusLearning\/","https:\/\/x.com\/imarticus","https:\/\/www.instagram.com\/imarticus\/","https:\/\/www.youtube.com\/@ImarticusLearningInstitute","https:\/\/in.linkedin.com\/company\/imarticuslearning"]},{"@type":"Person","@id":"https:\/\/imarticus.org\/blog\/author\/imarticus\/#author","url":"https:\/\/imarticus.org\/blog\/author\/imarticus\/","name":"Imarticus Learning","image":{"@type":"ImageObject","@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#authorImage","url":"https:\/\/secure.gravatar.com\/avatar\/26c0ffd94601c4078ba4c61e5b9dd95dbb2872f64fd398e2c5d020e8ff0eb581?s=96&d=mm&r=g","width":96,"height":96,"caption":"Imarticus Learning"}},{"@type":"WebPage","@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#webpage","url":"https:\/\/imarticus.org\/blog\/sql-functions\/","name":"SQL Functions Every Analyst Uses: Aggregate to Window","description":"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.","inLanguage":"en-GB","isPartOf":{"@id":"https:\/\/imarticus.org\/blog\/#website"},"breadcrumb":{"@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#breadcrumblist"},"author":{"@id":"https:\/\/imarticus.org\/blog\/author\/imarticus\/#author"},"creator":{"@id":"https:\/\/imarticus.org\/blog\/author\/imarticus\/#author"},"image":{"@type":"ImageObject","url":"https:\/\/imarticus.org\/blog\/wp-content\/uploads\/2026\/11\/sql-functions.webp","@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#mainImage","width":1200,"height":630},"primaryImageOfPage":{"@id":"https:\/\/imarticus.org\/blog\/sql-functions\/#mainImage"},"datePublished":"2026-11-03T15:46:01+05:30","dateModified":"2026-11-03T15:46:05+05:30"},{"@type":"WebSite","@id":"https:\/\/imarticus.org\/blog\/#website","url":"https:\/\/imarticus.org\/blog\/","name":"Imarticus Learning","inLanguage":"en-GB","publisher":{"@id":"https:\/\/imarticus.org\/blog\/#organization"}}]},"og:locale":"en_GB","og:site_name":"Imarticus Blog -","og:type":"article","og:title":"SQL Functions Every Analyst Uses: Aggregate to Window","og:description":"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.","og:url":"https:\/\/imarticus.org\/blog\/sql-functions\/","article:published_time":"2026-11-03T10:16:01+00:00","article:modified_time":"2026-11-03T10:16:05+00:00","article:publisher":"https:\/\/www.facebook.com\/ImarticusLearning\/","twitter:card":"summary_large_image","twitter:site":"@imarticus","twitter:title":"SQL Functions Every Analyst Uses: Aggregate to Window","twitter:description":"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.","twitter:creator":"@imarticus"},"aioseo_meta_data":{"post_id":"278755","title":"SQL Functions Every Analyst Uses: Aggregate to Window","description":"Learn SQL functions the easy way: aggregate, window, string, date and user defined functions, with simple examples and handy tips to write faster queries.","keywords":null,"keyphrases":{"focus":{"keyphrase":"sql functions","score":0,"analysis":[]},"additional":[]},"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":"","og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"BlogPosting","isEnabled":true},"graphs":[]},"schema_type":"default","schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":"-1","robots_max_videopreview":"-1","robots_max_imagepreview":"large","priority":null,"frequency":"default","local_seo":null,"breadcrumb_settings":null,"limit_modified_date":0,"ai":{"faqs":[],"keyPoints":[],"schemas":[],"titles":[],"descriptions":[],"socialPosts":{"email":{"subject":"","preview":"","content":""},"linkedin":[],"twitter":[],"facebook":[],"instagram":[]}},"created":"2026-11-03 09:43:57","updated":"2026-11-03 10:16:05","seo_analyzer_scan_date":null,"focus_keyword":"sql functions","additional_keywords":null,"truseo_locale":null},"aioseo_breadcrumb":"<div class=\"aioseo-breadcrumbs\"><span class=\"aioseo-breadcrumb\">\n\t\t\t<a href=\"https:\/\/imarticus.org\/blog\/\" title=\"Home\">Home<\/a>\n\t\t<\/span><span class=\"aioseo-breadcrumb-separator\">\u00bb<\/span><span class=\"aioseo-breadcrumb\">\n\t\t\t<a href=\"https:\/\/imarticus.org\/blog\/category\/data-analytics\/\" title=\"Data Analytics\">Data Analytics<\/a>\n\t\t<\/span><span class=\"aioseo-breadcrumb-separator\">\u00bb<\/span><span class=\"aioseo-breadcrumb\">\n\t\t\tSQL Functions That Make Data Analysis Easier\n\t\t<\/span><\/div>","aioseo_breadcrumb_json":[{"label":"Home","link":"https:\/\/imarticus.org\/blog\/"},{"label":"Data Analytics","link":"https:\/\/imarticus.org\/blog\/category\/data-analytics\/"},{"label":"SQL Functions That Make Data Analysis Easier","link":"https:\/\/imarticus.org\/blog\/sql-functions\/"}],"modified_by":"Hassa Shaikh","_links":{"self":[{"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/posts\/278755","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/comments?post=278755"}],"version-history":[{"count":5,"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/posts\/278755\/revisions"}],"predecessor-version":[{"id":278765,"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/posts\/278755\/revisions\/278765"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/media\/278760"}],"wp:attachment":[{"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/media?parent=278755"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/categories?post=278755"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/imarticus.org\/blog\/wp-json\/wp\/v2\/tags?post=278755"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}