Text Practice Mode
sql practice
created Feb 15th 2017, 07:21 by KAPIL ARORA
1
114 words
18 completed
3.5
Rating visible after 3 or more votes
saving score / loading statistics ...
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
saving score / loading statistics ...