Tekst Oefening Modus
sql practice
gemaakt Feb 15th 2017, 07:21 door KAPIL ARORA
1
114 woorden
20 voltooid
3.5
Beoordeling zichtbaar na 3 of meer stemmen
score opslaan / statistieken laden ...
00:00
SELECT
5 AS columnsCount,
DATE(date_added_on) AS timing,
COUNT(*) AS total_orders,
COUNT( CASE WHEN a.vendor_id IN (137, 203) THEN 1 ELSE NULL END ) AS fatafat_orders,
COUNT( CASE WHEN a.vendor_id NOT IN (137,203,359) THEN 1 ELSE NULL END ) AS non_fatafat_orders,
COUNT(CASE WHEN first_order_on IS NOT NULL
AND DATE(a.date_added_on) = first_order_on THEN 1 ELSE NULL END ) AS new_vendor_orders,
COUNT( CASE WHEN a.vendor_id = 359 THEN 1 ELSE NULL END ) AS meals_orders
FROM
jugnoo_dodo_live.tb_tasks AS a
LEFT JOIN (
SELECT
DATE(MIN(date_added_on)) AS first_order_on,
vendor_id
FROM
jugnoo_dodo_live.`tb_tasks`
WHERE
status=2
GROUP BY
vendor_id) AS b
ON
a.vendor_id = b.vendor_id
WHERE
status = 2 AND date_added_on >= '2016-06-01'
AND city ~city~
GROUP BY
timing
ORDER BY
timing
5 AS columnsCount,
DATE(date_added_on) AS timing,
COUNT(*) AS total_orders,
COUNT( CASE WHEN a.vendor_id IN (137, 203) THEN 1 ELSE NULL END ) AS fatafat_orders,
COUNT( CASE WHEN a.vendor_id NOT IN (137,203,359) THEN 1 ELSE NULL END ) AS non_fatafat_orders,
COUNT(CASE WHEN first_order_on IS NOT NULL
AND DATE(a.date_added_on) = first_order_on THEN 1 ELSE NULL END ) AS new_vendor_orders,
COUNT( CASE WHEN a.vendor_id = 359 THEN 1 ELSE NULL END ) AS meals_orders
FROM
jugnoo_dodo_live.tb_tasks AS a
LEFT JOIN (
SELECT
DATE(MIN(date_added_on)) AS first_order_on,
vendor_id
FROM
jugnoo_dodo_live.`tb_tasks`
WHERE
status=2
GROUP BY
vendor_id) AS b
ON
a.vendor_id = b.vendor_id
WHERE
status = 2 AND date_added_on >= '2016-06-01'
AND city ~city~
GROUP BY
timing
ORDER BY
timing
score opslaan / statistieken laden ...