SELECT
  customer_id,
  customer_name,
  COUNT(order_id) as total
FROM
  customers
  INNER JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY
  customer_id,
  customer_name
HAVING
  COUNT(order_id) > 5
ORDER BY
  COUNT(order_id) DESC;
---

---
UPDATE
  customers
SET
  totalorders = ordersummary.total
FROM
  (
    SELECT
      customer_id,
      count(order_id) As total
    FROM
      orders
    GROUP BY
      customer_id
  ) As ordersummary
WHERE
  customers.customer_id = ordersummary.customer_id
---
SELECT
  *
FROM
  sometable
UNION ALL
SELECT
  *
FROM
  someothertable;
---
SET
  NAMES 'utf8';
---
CREATE TABLE `PREFIX_address` (
  `id_address` int(10) unsigned NOT NULL auto_increment,
  `id_country` int(10) unsigned NOT NULL,
  `id_state` int(10) unsigned default NULL,
  `id_customer` int(10) unsigned NOT NULL default '0',
  `id_manufacturer` int(10) unsigned NOT NULL default '0',
  `id_supplier` int(10) unsigned NOT NULL default '0',
  `id_warehouse` int(10) unsigned NOT NULL default '0',
  `alias` varchar(32) NOT NULL,
  `company` varchar(64) default NULL,
  `lastname` varchar(32) NOT NULL,
  `firstname` varchar(32) NOT NULL,
  `address1` varchar(128) NOT NULL,
  `address2` varchar(128) default NULL,
  `postcode` varchar(12) default NULL,
  `city` varchar(64) NOT NULL,
  `other` text,
  `phone` varchar(16) default NULL,
  `phone_mobile` varchar(16) default NULL,
  `vat_number` varchar(32) default NULL,
  `dni` varchar(16) DEFAULT NULL,
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  `active` tinyint(1) unsigned NOT NULL default '1',
  `deleted` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY (`id_address`),
  KEY `address_customer` (`id_customer`),
  KEY `id_country` (`id_country`),
  KEY `id_state` (`id_state`),
  KEY `id_manufacturer` (`id_manufacturer`),
  KEY `id_supplier` (`id_supplier`),
  KEY `id_warehouse` (`id_warehouse`)
) ENGINE = ENGINE_TYPE DEFAULT CHARSET = utf8
---
CREATE TABLE `PREFIX_alias` (
  `id_alias` int(10) unsigned NOT NULL auto_increment,
  `alias` varchar(255) NOT NULL,
  `search` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY (`id_alias`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE = ENGINE_TYPE DEFAULT CHARSET = utf8
---
CREATE TABLE `PREFIX_carrier` (
  `id_carrier` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_reference` int(10) unsigned NOT NULL,
  `id_tax_rules_group` int(10) unsigned DEFAULT '0',
  `name` varchar(64) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `shipping_handling` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `range_behavior` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_module` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_free` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `shipping_external` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `need_range` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `external_module_name` varchar(64) DEFAULT NULL,
  `shipping_method` int(2) NOT NULL DEFAULT '0',
  `position` int(10) unsigned NOT NULL default '0',
  `max_width` int(10) DEFAULT 0,
  `max_height` int(10) DEFAULT 0,
  `max_depth` int(10) DEFAULT 0,
  `max_weight` int(10) DEFAULT 0,
  `grade` int(10) DEFAULT 0,
  PRIMARY KEY (`id_carrier`),
  KEY `deleted` (`deleted`, `active`),
  KEY `id_tax_rules_group` (`id_tax_rules_group`)
) ENGINE = ENGINE_TYPE DEFAULT CHARSET = utf8
---
CREATE TABLE IF NOT EXISTS `PREFIX_specific_price_rule` (
  `id_specific_price_rule` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_currency` int(10) unsigned NOT NULL,
  `id_country` int(10) unsigned NOT NULL,
  `id_group` int(10) unsigned NOT NULL,
  `from_quantity` mediumint(8) unsigned NOT NULL,
  `price` DECIMAL(20, 6),
  `reduction` decimal(20, 6) NOT NULL,
  `reduction_type` enum('amount', 'percentage') NOT NULL,
  `from` datetime NOT NULL,
  `to` datetime NOT NULL,
  PRIMARY KEY (`id_specific_price_rule`),
  KEY `id_product` (
    `id_shop`, `id_currency`, `id_country`,
    `id_group`, `from_quantity`, `from`,
    `to`
  )
) ENGINE = ENGINE_TYPE DEFAULT CHARSET = utf8
---
UPDATE
  `PREFIX_configuration`
SET
  value = '6'
WHERE
  name = 'PS_SEARCH_WEIGHT_PNAME'
---
UPDATE
  `PREFIX_hook_module`
SET
  position = 1
WHERE
  id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayPayment'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'cheque'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayPaymentReturn'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'cheque'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayHome'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'homeslider'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'actionAuthentication'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'statsdata'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'actionShopDataDuplication'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'homeslider'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayTop'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'blocklanguages'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'actionCustomerAccountAdd'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'statsdata'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayCustomerAccount'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'favoriteproducts'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayAdminStatsModules'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'statsvisits'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayAdminStatsGraphEngine'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'graphvisifire'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayAdminStatsGridEngine'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'gridhtml'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayLeftColumnProduct'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'blocksharefb'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'actionSearch'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'statssearch'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'actionCategoryAdd'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'blockcategories'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'actionCategoryUpdate'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'blockcategories'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'actionCategoryDelete'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'blockcategories'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'actionAdminMetaSave'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'blockcategories'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayMyAccountBlock'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'favoriteproducts'
  )
  OR id_hook = (
    SELECT
      id_hook
    FROM
      `PREFIX_hook`
    WHERE
      name = 'displayFooter'
  )
  AND id_module = (
    SELECT
      id_module
    FROM
      `PREFIX_module`
    WHERE
      name = 'blockreinsurance'
  )
---
ALTER TABLE
  `PREFIX_employee`
ADD
  `bo_color` varchar(32) default NULL AFTER `stats_date_to`
---
INSERT INTO `PREFIX_cms_category_lang`
VALUES
  (
    1, 3, 'Inicio', '', 'home', NULL, NULL,
    NULL
  )
---
INSERT INTO `PREFIX_cms_category`
VALUES
  (
    1,
    0,
    0,
    1,
    NOW(),
    NOW(),
    0
  )
---
UPDATE
  `PREFIX_cms_category`
SET
  `position` = 0
---
ALTER TABLE
  `PREFIX_customer`
ADD
  `note` text AFTER `secure_key`
---
ALTER TABLE
  `PREFIX_contact`
ADD
  `customer_service` tinyint(1) NOT NULL DEFAULT 0 AFTER `email`
---
INSERT INTO `PREFIX_specific_price` (
  `id_product`, `id_shop`, `id_currency`,
  `id_country`, `id_group`, `priority`,
  `price`, `from_quantity`, `reduction`,
  `reduction_type`, `from`, `to`
) (
  SELECT
    dq.`id_product`,
    1,
    1,
    0,
    1,
    0,
    0.00,
    dq.`quantity`,
    IF(
      dq.`id_discount_type` = 2, dq.`value`,
      dq.`value` / 100
    ),
    IF (
      dq.`id_discount_type` = 2, 'amount',
      'percentage'
    ),
    '0000-00-00 00:00:00',
    '0000-00-00 00:00:00'
  FROM
    `PREFIX_discount_quantity` dq
    INNER JOIN `PREFIX_product` p ON (p.`id_product` = dq.`id_product`)
)
---
DROP
  TABLE `PREFIX_discount_quantity`
---
INSERT INTO `PREFIX_specific_price` (
  `id_product`, `id_shop`, `id_currency`,
  `id_country`, `id_group`, `priority`,
  `price`, `from_quantity`, `reduction`,
  `reduction_type`, `from`, `to`
) (
  SELECT
    p.`id_product`,
    1,
    0,
    0,
    0,
    0,
    0.00,
    1,
    IF(
      p.`reduction_price` > 0, p.`reduction_price`,
      p.`reduction_percent` / 100
    ),
    IF(
      p.`reduction_price` > 0, 'amount',
      'percentage'
    ),
    IF (
      p.`reduction_from` = p.`reduction_to`,
      '0000-00-00 00:00:00', p.`reduction_from`
    ),
    IF (
      p.`reduction_from` = p.`reduction_to`,
      '0000-00-00 00:00:00', p.`reduction_to`
    )
  FROM
    `PREFIX_product` p
  WHERE
    p.`reduction_price`
    OR p.`reduction_percent`
)
---
ALTER TABLE
  `PREFIX_product`
DROP
  `reduction_price`,
DROP
  `reduction_percent`,
DROP
  `reduction_from`,
DROP
  `reduction_to`
---
INSERT INTO `PREFIX_configuration` (
  `name`, `value`, `date_add`, `date_upd`
)
VALUES
  (
    'PS_SPECIFIC_PRICE_PRIORITIES',
    'id_shop;id_currency;id_country;id_group',
    NOW(),
    NOW()
  ),
  (
    'PS_TAX_DISPLAY',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_SMARTY_FORCE_COMPILE',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_DISTANCE_UNIT',
    'km',
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_DISPLAY_CMS',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_DISPLAY_FOOTER',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STORES_SIMPLIFIED',
    0,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_CUSTOMER_PAGESVIEWS',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_PAGESVIEWS',
    1,
    NOW(),
    NOW()
  ),
  (
    'PS_STATSDATA_PLUGINS',
    1,
    NOW(),
    NOW()
  )
---
INSERT INTO `PREFIX_configuration` (
  `name`, `value`, `date_add`, `date_upd`
)
VALUES
  (
    'PS_CONDITIONS_CMS_ID',
    IFNULL(
      (
        SELECT
          `id_cms`
        FROM
          `PREFIX_cms`
        WHERE
          `id_cms` = 3
      ),
      0
    ),
    NOW(),
    NOW()
  )
---
CREATE TEMPORARY TABLE `PREFIX_configuration_tmp` (`value` text)
---
SET
  @defaultOOS = (
    SELECT
      value
    FROM
      `PREFIX_configuration`
    WHERE
      name = 'PS_ORDER_OUT_OF_STOCK'
  )
---
UPDATE
  `PREFIX_product` p
SET
  `cache_default_attribute` = 0
WHERE
  `id_product` NOT IN (
    SELECT
      `id_product`
    FROM
      `PREFIX_product_attribute`
  )
---
INSERT INTO `PREFIX_hook` (
  `name`, `title`, `description`, `position`
)
VALUES
  (
    'processCarrier', 'Carrier Process',
    NULL, 0
  )
---
INSERT INTO `PREFIX_stock_mvt_reason_lang` (
  `id_stock_mvt_reason`, `id_lang`,
  `name`
)
VALUES
  (1, 1, 'Order'),
  (1, 2, 'Commande'),
  (2, 1, 'Missing Stock Movement'),
  (
    2, 2, 'Mouvement de stock manquant'
  ),
  (3, 1, 'Restocking'),
  (3, 2, 'Réassort')
---
INSERT INTO `PREFIX_meta_lang` (
  `id_lang`, `id_meta`, `title`, `url_rewrite`
)
VALUES
  (
    1,
    (
      SELECT
        `id_meta`
      FROM
        `PREFIX_meta`
      WHERE
        `page` = 'authentication'
    ),
    'Authentication',
    'authentication'
  ),
  (
    2,
    (
      SELECT
        `id_meta`
      FROM
        `PREFIX_meta`
      WHERE
        `page` = 'authentication'
    ),
    'Authentification',
    'authentification'
  ),
  (
    3,
    (
      SELECT
        `id_meta`
      FROM
        `PREFIX_meta`
      WHERE
        `page` = 'authentication'
    ),
    'Autenticación',
    'autenticacion'
  )
---
LOCK TABLES `admin_assert` WRITE
---
UNLOCK TABLES
---
DROP
  TABLE IF EXISTS `admin_role`
---
SELECT
  *
FROM
  -- This is another comment
  MyTable # One final comment

  /* This is a block comment
  */
WHERE
  1 = 2;
---
SELECT
  -- This is a test
---
SELECT
  Test
FROM
  Test
WHERE
  (MyColumn = 1))
AND (
  (
    (SomeOtherColumn = 2); WARNING: unclosed parentheses or section
---
ALTER TABLE
  `test_modify`
MODIFY
  `id` INT(11) UNSIGNED NOT NULL;
---
ALTER TABLE
  `test_change`
CHANGE
  `id` `_id` BIGINT(20) UNSIGNED NULL;
---
SELECT
  *
LIMIT
  1;
SELECT
  a,
  b,
  c,
  d
FROM
  e
LIMIT
  1, 2;
SELECT
  1,
  2,
  3
WHERE
  a in (1, 2, 3, 4, 5)
  and b = 5;
---
SELECT
  count - 50
WHERE
  a - 50 = b
WHERE
  1
  and -50
WHERE
  -50 = a
WHERE
  a = -50
WHERE
  1
  /*test*/
  -50
WHERE
  1
  and -50;
---
SELECT
  @
  and b;
---
SELECT
  @"weird variable name";
---
SELECT
  :pdoParam;
---
SELECT
  "no closing quote
---
SELECT
  [sqlserver]
FROM
  [escap[e]]d style];
---
SELECT
  a
FROM
  b
  LEFT OUTER JOIN c on (d = f);
---
WITH
  cte AS (
    SELECT
      a,
      b
    FROM
      table
  ),
  RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
    SELECT
      1,
      0,
      1
    UNION ALL
    SELECT
      n + 1,
      next_fib_n,
      fib_n + next_fib_n
    FROM
      fibonacci
    WHERE
      n < 10
  )
SELECT
  *
FROM
  fibonacci;
---
WITH
  cte1 AS (
    SELECT
      a,
      b
    FROM
      table1
  ),
  cte2 AS (
    SELECT
      c,
      d
    FROM
      table2
  )
SELECT
  b,
  d
FROM
  cte1
  JOIN cte2
WHERE
  cte1.a = cte2.c;
---
SELECT
  a,
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c
    GROUPS
      BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
      EXCLUDE NO OTHERS
  ) AS no_others,
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c
    GROUPS
      BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
      EXCLUDE CURRENT ROW
  ) AS current_row,
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c
    GROUPS
      BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
      EXCLUDE GROUP
  ) AS grp,
  GROUP_CONCAT(b, '.') OVER (
    ORDER BY
      c
    GROUPS
      BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
      EXCLUDE TIES
  ) AS tie,
  GROUP_CONCAT(b, '.') FILTER (
    WHERE
      c != 'two'
  ) OVER (
    ORDER BY
      a
  ) AS filtered,
  CONVERT(
    VARCHAR(20),
    AVG(SalesYTD) OVER (
      PARTITION BY
        TerritoryID
      ORDER BY
        DATEPART(yy, ModifiedDate)
    ),
    1
  ) AS MovingAvg,
  AVG(starting_salary) OVER w2 AVG,
  MIN(starting_salary) OVER w2 MIN_STARTING_SALARY,
  MAX(starting_salary) OVER (
    w1
    ORDER BY
      hire_date
  ),
  LISTAGG(arg, ',') OVER (
    PARTITION BY
      part
    ORDER BY
      ord
    ROWS
      BETWEEN 1 PRECEDING
      AND 1 FOLLOWING
  ) AS LISTAGG_ROWS,
  LISTAGG(arg, ',') OVER (
    PARTITION BY
      part
    ORDER BY
      ord
    RANGE
      BETWEEN 1 PRECEDING
      AND 1 FOLLOWING
  ) AS LISTAGG_RANGE,
  MIN(Revenue) OVER (
    PARTITION BY
      DepartmentID
    ORDER BY
      RevenueYear
    ROWS
      BETWEEN CURRENT ROW
      AND UNBOUNDED FOLLOWING
  ) AS MinRevenueBeyond
FROM
  t1
WINDOW
  w1 AS (
    PARTITION BY
      department,
      division
  ),
  w2 AS (
    w1
    ORDER BY
      hire_date
  );
---
SELECT
  1 :: text;
---
-- semicolon must decrease special indentation level
MY_NON_TOP_LEVEL_KEYWORD_FX_1();
MY_NON_TOP_LEVEL_KEYWORD_FX_2();
SELECT
  x
FROM
  (
    SELECT
      1 as x
  );
MY_NON_TOP_LEVEL_KEYWORD_FX_3();
BEGIN MY_NON_TOP_LEVEL_KEYWORD_FX_4();
MY_NON_TOP_LEVEL_KEYWORD_FX_5();
END;
BEGIN
SELECT
  x
FROM
  (
    SELECT
      1 as x
  );
MY_NON_TOP_LEVEL_KEYWORD_FX_6();
END;
---
SELECT
  a
FROM
  test
  STRAIGHT_JOIN test2 ON test.id = test2.id