API tools faq
paste
Advertisement
sarthakpoddar

Trade Cohort + Lane

sarthakpoddar
Jun 30th, 2025
12
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 21.51 KB | None | 0 0
raw download clone embed print report
  1. WITH
  2. -- 1. Organizations
  3. orgs AS (
  4. SELECT DISTINCT
  5. organization_id,business_name,
  6. lead_organization_id,registration_number,
  7. ihls_overall_strength,ihls_trade_category,
  8. multiIf(org_bookings_created = 0, 'No Shipment',
  9. org_bookings_created = 1 AND last_booking_at >= '2024-11-01','Single Shipment',
  10. org_bookings_created > 1 AND last_booking_at >= '2024-11-01','Multi Shipment',
  11. 'Old Shipment'
  12. ) AS shipment_status,
  13. multiIf(
  14. org_enquiries_created = 0, 'No Enquiry',
  15. org_enquiries_created = 1 AND cs.last_enquiries_created_at >= '2024-11-01', 'Single Enquiry',
  16. org_enquiries_created > 1 AND cs.last_enquiries_created_at >= '2024-11-01', 'Multi Enquiry',
  17. 'Old Enquiry'
  18. ) AS enquiry_status
  19. FROM kavach.customer_statistics AS cs FINAL
  20. WHERE account_type IN ('importer_exporter','importer','exporter')
  21. AND org_status != 'inactive'
  22. AND organization_id != '00000000-0000-0000-0000-000000000000'
  23. ),
  24. stakeholders as
  25. (
  26. SELECT
  27. DISTINCT cusv.organization_id,
  28. CASE
  29. WHEN
  30. countIf(
  31. cusv.contact_type = 'contact_number'
  32. AND cusv.contact_country_code LIKE '%91%'
  33. AND cusv.contact_stage != 'system_invalidated'
  34. ) > 0
  35. THEN 'Yes' ELSE 'No'
  36. END AS ind_contact,
  37. max(cusv.sales_agent_id) AS user_id,
  38. max(cusv.sales_agent) AS cse
  39. FROM kavach.customer_user_statistics_v2 AS cusv final
  40. GROUP BY cusv.organization_id
  41. ),
  42. shipments AS (
  43. SELECT
  44. lead_id,
  45. registration_number,
  46. shipment_year,
  47. incoterm,
  48. shipment_mode,
  49. SUM(shipment_count) AS overall,
  50. SUMIf(shipment_count, origin_country_id = '541d1232-58ce-4d64-83d6-556a42209eb7' AND LOWER(shipment_mode) = 'sea') AS ocean_outbound,
  51. SUMIf(shipment_count, LOWER(shipment_mode) = 'air') AS air_outbound_shipments,
  52. SUMIf(shipment_count, origin_country_id IN (
  53. '1b94734e-7d51-4e94-9dd2-ef96aee64a8f', '177fcbad-8ef7-4324-871c-6c31745f4411',
  54. '61a683f3-128b-4193-98f7-dd72f68db03d', '6e18d508-87b9-4e7e-a785-b47edc76b0b7',
  55. '7552270b-caae-4715-acd5-1da117c5434f', '2693fa76-6539-410d-a0b0-551d9e620ba3'
  56. ) AND LOWER(shipment_mode) = 'air' AND destination_country_id = '541d1232-58ce-4d64-83d6-556a42209eb7') AS east_asia_air,
  57. SUMIf(shipment_count, origin_country_id IN (
  58. '1b94734e-7d51-4e94-9dd2-ef96aee64a8f', '177fcbad-8ef7-4324-871c-6c31745f4411',
  59. '61a683f3-128b-4193-98f7-dd72f68db03d', '6e18d508-87b9-4e7e-a785-b47edc76b0b7',
  60. '7552270b-caae-4715-acd5-1da117c5434f', '2693fa76-6539-410d-a0b0-551d9e620ba3'
  61. ) AND LOWER(shipment_mode) = 'sea' AND destination_country_id = '541d1232-58ce-4d64-83d6-556a42209eb7') AS east_asia_sea,
  62. SUMIf(shipment_count, LOWER(shipment_mode) = 'air' AND (
  63. -- Meat and Edible Meat Offal
  64. toString(commodity) LIKE '0201%' OR toString(commodity) LIKE '0202%' OR
  65. toString(commodity) LIKE '0203%' OR toString(commodity) LIKE '0204%' OR
  66. toString(commodity) LIKE '0207%' OR toString(commodity) LIKE '0208%' OR toString(commodity) LIKE '0209%' OR
  67. -- Fish and Seafood
  68. toString(commodity) LIKE '0301%' OR toString(commodity) LIKE '0302%' OR
  69. toString(commodity) LIKE '0303%' OR toString(commodity) LIKE '0304%' OR
  70. toString(commodity) LIKE '0306%' OR toString(commodity) LIKE '0307%' OR
  71. -- Dairy Products
  72. toString(commodity) LIKE '0402%' OR toString(commodity) LIKE '0405%' OR toString(commodity) LIKE '0406%' OR
  73. -- Live Plants
  74. toString(commodity) LIKE '0602%' OR
  75. -- Vegetables
  76. toString(commodity) LIKE '0701%' OR
  77. toString(commodity) LIKE '0702%' OR
  78. toString(commodity) LIKE '0703%' OR
  79. toString(commodity) LIKE '0704%' OR
  80. toString(commodity) LIKE '0705%' OR
  81. toString(commodity) LIKE '0706%' OR
  82. toString(commodity) LIKE '0707%' OR
  83. toString(commodity) LIKE '0708%' OR
  84. toString(commodity) LIKE '0709%' OR
  85. toString(commodity) LIKE '0710%' OR
  86. toString(commodity) LIKE '0711%' OR
  87. toString(commodity) LIKE '0712%' OR
  88. toString(commodity) LIKE '0713%' OR
  89. toString(commodity) LIKE '0714%' OR
  90. -- Fruits and Nuts
  91. toString(commodity) LIKE '0801%' OR
  92. toString(commodity) LIKE '0802%' OR
  93. toString(commodity) LIKE '0803%' OR
  94. toString(commodity) LIKE '0804%' OR
  95. toString(commodity) LIKE '0805%' OR
  96. toString(commodity) LIKE '0806%' OR
  97. toString(commodity) LIKE '0807%' OR
  98. toString(commodity) LIKE '0808%' OR
  99. toString(commodity) LIKE '0809%' OR
  100. toString(commodity) LIKE '0810%' OR
  101. toString(commodity) LIKE '0811%' OR
  102. toString(commodity) LIKE '0812%' OR
  103. toString(commodity) LIKE '0813%' OR
  104. -- Miscellaneous Edible Preparations
  105. toString(commodity) LIKE '2106%'
  106. )) AS air_outbound_perishable_shipments,
  107. SUMIf(shipment_count,origin_country_id IN ('1b94734e-7d51-4e94-9dd2-ef96aee64a8f')
  108. AND destination_country_id IN ('541d1232-58ce-4d64-83d6-556a42209eb7')) AS cn_in,
  109. SUMIf(shipment_count,origin_country_id
  110. IN ('177fcbad-8ef7-4324-871c-6c31745f4411', --Vietnam
  111. '61a683f3-128b-4193-98f7-dd72f68db03d', --Thailand
  112. '6e18d508-87b9-4e7e-a785-b47edc76b0b7', --Singapore
  113. '7552270b-caae-4715-acd5-1da117c5434f', --Malaysia
  114. 'bd771cfe-8ffa-4400-85ca-fccfc0ae33f1', --SouthKorea
  115. 'd9ad9e8c-f1a4-408a-b3fb-f83d12587aaf', --Japan
  116. '2693fa76-6539-410d-a0b0-551d9e620ba3' --Indonesia
  117. ) AND destination_country_id IN ('541d1232-58ce-4d64-83d6-556a42209eb7')) AS sea_in,
  118. SUMIf(shipment_count,origin_country_id IN ('541d1232-58ce-4d64-83d6-556a42209eb7')
  119. AND destination_country_id IN ('fe92b7c7-9481-4a3b-8d79-df9a7bf94a4e', --US
  120. 'e8a9b2e8-5fdc-41d0-bb87-3191029649d1' --Canada
  121. )) AS in_us,
  122. SUMIf(shipment_count,origin_country_id IN ('541d1232-58ce-4d64-83d6-556a42209eb7')
  123. AND destination_country_id IN ('345f3aa9-ae78-40cf-b70a-fc5c3af2af99', --UAE
  124. 'de66d06c-bd96-4a69-8976-81fb6c16538e', --Saudi
  125. 'cd326552-e99d-4e09-9c6c-372371921fe9', --Kuwait
  126. '47470b03-5922-4df9-a8a9-0782c3e86074', --Qatar
  127. '82ac4101-aa13-4cd4-90e1-f3e859656477', --Oman
  128. '4241f4d4-c791-4cc0-b78f-98efaaf157f6', --Bahrain
  129. '79d361d6-8157-4df2-9705-a2e17ce14a26' --Sudan
  130. )) AS in_gulf,
  131. SUMIf(shipment_count,origin_country_id IN ('541d1232-58ce-4d64-83d6-556a42209eb7')
  132. AND destination_country_id IN ('3a8af1c5-960c-4765-81e2-d20e3c499c2f', --Kenya
  133. 'b298143b-2ca1-4dfa-aa64-4151a2498fd9', --Tanzania
  134. '724a3ef0-6333-4c2a-8019-8c0401356c35', --Uganda
  135. 'f0455923-373b-4249-a6d3-a27a6a87f215', --Ethiopia
  136. '790d80b3-ed46-45a2-8510-3f337ec1b7ab' --Mozambique
  137. )) AS in_eafrica,
  138. SUMIf(shipment_count,origin_country_id IN ('541d1232-58ce-4d64-83d6-556a42209eb7')
  139. AND destination_country_id IN ('71bde190-f4ce-464b-b71b-c0935c1308c6', --Nigeria
  140. 'c1f8e2b3-d5b0-41ba-99b8-4133db701a5d', --Ghana
  141. 'd7c3b525-dacc-4b50-abc4-ea063d466932', --IvoryCoast
  142. 'f5e623ba-ab71-46d5-94ae-a4751747d252', --Senegal
  143. 'c1692c66-bcc6-45a3-8ae8-ef6f95cdbd31', --Togo
  144. 'acd25c39-19fd-484d-83a9-05363c1b0d4b' --Benin
  145. )) AS in_wafrica,
  146. SUMIf(shipment_count,origin_country_id IN ('541d1232-58ce-4d64-83d6-556a42209eb7')
  147. AND destination_country_id IN ('da61a027-a467-4565-acfb-00783932a191', --Czech Republic
  148. '60c3faf4-302b-42be-beb0-47f3e12aed5d', --France
  149. 'd4548d60-b291-4975-97b9-f320af042560', --Germany
  150. '8114bfac-e913-45ac-aae6-fac3a50d5a25', --Hungary
  151. '3fef4273-e6f5-4ee6-b125-55a6938fc126', --Ireland
  152. 'f0c113f4-c65e-429c-be94-046fe5348a92', --Poland
  153. '53f3d322-b6ed-4335-b113-68a1f637437b', --Slovakia
  154. 'c0032d9a-4872-4ab0-9cea-369bbdb28604', --Spain
  155. '222d4b9d-56a8-4580-b761-a71c653263fb' --UK
  156. )) AS in_europe
  157. FROM kavach.lead_shipment_commodity_stats
  158. WHERE shipment_year IN ('2024', '2025')
  159. GROUP BY lead_id, registration_number, shipment_year, incoterm, shipment_mode
  160. ),
  161. -- 3. Aggregation logic by org
  162. aggregations AS (
  163. SELECT
  164. org.organization_id,
  165. SUMIf(s.overall, s.shipment_year IN ('2024', '2025')) AS overall_shipment,
  166. SUM(s.east_asia_sea)east_asia_sea,
  167. SUM(s.east_asia_air)east_asia_air,
  168. SUM(s.ocean_outbound)ocean_outbound,
  169. SUM(s.air_outbound_perishable_shipments)air_outbound_perishable_shipments,
  170. SUM(s.air_outbound_shipments)air_outbound_shipments,
  171. CASE
  172. WHEN SUMIf(s.overall, s.shipment_year IN ('2024', '2025')) >= 480 THEN 'Super Large'
  173. WHEN SUMIf(s.overall, s.shipment_year IN ('2024', '2025')) >= 120 THEN 'Large'
  174. WHEN SUMIf(s.overall, s.shipment_year IN ('2024', '2025')) BETWEEN 48 AND 119 THEN 'Medium'
  175. WHEN SUMIf(s.overall, s.shipment_year IN ('2024', '2025')) BETWEEN 9 AND 47 THEN 'Small'
  176. WHEN SUMIf(s.overall, s.shipment_year IN ('2024', '2025')) BETWEEN 0 AND 9 THEN 'Micro'
  177. ELSE NULL
  178. END AS overall_strength,
  179.  
  180. CASE
  181. WHEN SUMIf(s.air_outbound_shipments, s.shipment_year IN ('2024', '2025')) >= 480 THEN 'Super Large'
  182. WHEN SUMIf(s.air_outbound_shipments, s.shipment_year IN ('2024', '2025')) >= 120 THEN 'Large'
  183. WHEN SUMIf(s.air_outbound_shipments, s.shipment_year IN ('2024', '2025')) BETWEEN 48 AND 119 THEN 'Medium'
  184. WHEN SUMIf(s.air_outbound_shipments, s.shipment_year IN ('2024', '2025')) BETWEEN 9 AND 47 THEN 'Small'
  185. WHEN SUMIf(s.air_outbound_shipments, s.shipment_year IN ('2024', '2025')) BETWEEN 0 AND 9 THEN 'Micro'
  186. ELSE NULL
  187. END AS overall_strength_ao,
  188.  
  189. CASE
  190. WHEN SUMIf(s.air_outbound_perishable_shipments, s.shipment_year IN ('2024', '2025')) >= 480 THEN 'Super Large'
  191. WHEN SUMIf(s.air_outbound_perishable_shipments, s.shipment_year IN ('2024', '2025')) >= 120 THEN 'Large'
  192. WHEN SUMIf(s.air_outbound_perishable_shipments, s.shipment_year IN ('2024', '2025')) BETWEEN 48 AND 119 THEN 'Medium'
  193. WHEN SUMIf(s.air_outbound_perishable_shipments, s.shipment_year IN ('2024', '2025')) BETWEEN 9 AND 47 THEN 'Small'
  194. WHEN SUMIf(s.air_outbound_perishable_shipments, s.shipment_year IN ('2024', '2025')) BETWEEN 0 AND 9 THEN 'Micro'
  195. ELSE NULL
  196. END AS overall_strength_ao_p,
  197.  
  198. CASE
  199. WHEN SUMIf(s.east_asia_air, s.shipment_year IN ('2024', '2025')) >= 480 THEN 'Super Large'
  200. WHEN SUMIf(s.east_asia_air, s.shipment_year IN ('2024', '2025')) >= 120 THEN 'Large'
  201. WHEN SUMIf(s.east_asia_air, s.shipment_year IN ('2024', '2025')) BETWEEN 48 AND 119 THEN 'Medium'
  202. WHEN SUMIf(s.east_asia_air, s.shipment_year IN ('2024', '2025')) BETWEEN 9 AND 47 THEN 'Small'
  203. WHEN SUMIf(s.east_asia_air, s.shipment_year IN ('2024', '2025')) BETWEEN 0 AND 9 THEN 'Micro'
  204. ELSE NULL
  205. END AS overall_strength_ea_ai,
  206.  
  207. CASE
  208. WHEN SUMIf(s.east_asia_sea, s.shipment_year IN ('2024', '2025')) >= 480 THEN 'Super Large'
  209. WHEN SUMIf(s.east_asia_sea, s.shipment_year IN ('2024', '2025')) >= 120 THEN 'Large'
  210. WHEN SUMIf(s.east_asia_sea, s.shipment_year IN ('2024', '2025')) BETWEEN 48 AND 119 THEN 'Medium'
  211. WHEN SUMIf(s.east_asia_sea, s.shipment_year IN ('2024', '2025')) BETWEEN 9 AND 47 THEN 'Small'
  212. WHEN SUMIf(s.east_asia_sea, s.shipment_year IN ('2024', '2025')) BETWEEN 0 AND 9 THEN 'Micro'
  213. ELSE NULL
  214. END AS overall_strength_ea_oi,
  215.  
  216. CASE
  217. WHEN SUMIf(s.ocean_outbound, s.shipment_year IN ('2024', '2025')) >= 480 THEN 'Super Large'
  218. WHEN SUMIf(s.ocean_outbound, s.shipment_year IN ('2024', '2025')) >= 120 THEN 'Large'
  219. WHEN SUMIf(s.ocean_outbound, s.shipment_year IN ('2024', '2025')) BETWEEN 48 AND 119 THEN 'Medium'
  220. WHEN SUMIf(s.ocean_outbound, s.shipment_year IN ('2024', '2025')) BETWEEN 9 AND 47 THEN 'Small'
  221. WHEN SUMIf(s.ocean_outbound, s.shipment_year IN ('2024', '2025')) BETWEEN 0 AND 9 THEN 'Micro'
  222. ELSE NULL
  223. END AS overall_strength_oo,
  224.  
  225. /* ───────── trade_category ────────── */
  226. /* ────────────────────────────────── trade_category ───────────────────────────────── */
  227. CASE
  228. /* ---------- 0. nothing at all -------------------------------------------------------- */
  229. WHEN coalesce( SUM(s.east_asia_sea) ,0)
  230. + coalesce( SUM(s.east_asia_air) ,0)
  231. + coalesce( SUM(s.ocean_outbound) ,0)
  232. + coalesce( SUM(s.air_outbound_shipments) ,0)
  233. + coalesce( SUM(s.air_outbound_perishable_shipments) ,0) = 0
  234. THEN NULL
  235.  
  236. /* ---------- 1. IMPORT volume ≥ OUTBOUND volume -------------------------------------- */
  237. WHEN coalesce(SUM(s.east_asia_sea),0) + coalesce(SUM(s.east_asia_air),0)
  238. >= coalesce(SUM(s.ocean_outbound),0)
  239. + coalesce(SUM(s.air_outbound_shipments),0)
  240. + coalesce(SUM(s.air_outbound_perishable_shipments),0)
  241. THEN
  242. /* choose the larger of EA-OI (sea) vs EA-AI (air) */
  243. CASE
  244. /* ── EA-OI wins ─────────────────────────────────────────────── */
  245. WHEN coalesce(SUM(s.east_asia_sea),0) >= coalesce(SUM(s.east_asia_air),0)
  246. THEN
  247. CASE
  248. WHEN ROUND(
  249. SUMIf(s.east_asia_sea , lower(s.incoterm) = 'fob')
  250. / NULLIF(SUM(s.overall),0) * 100 , 2 ) >= 15
  251. THEN 'EA_OI_FOB' ELSE 'EA_OI_CIF' END
  252. /* ── EA-AI wins ─────────────────────────────────────────────── */
  253. ELSE
  254. CASE
  255. WHEN ROUND(
  256. SUMIf(s.east_asia_air , lower(s.incoterm) = 'fob')
  257. / NULLIF(SUM(s.overall),0) * 100 , 2 ) >= 15
  258. THEN 'EA_AI_FOB' ELSE 'EA_AI_CIF' END
  259. END
  260.  
  261. /* ---------- 2. OUTBOUND volume dominates ------------------------------------------- */
  262. ELSE
  263. /* identify which outbound lane is really the biggest (ties ≈ first match) */
  264. CASE
  265. WHEN coalesce(SUM(s.ocean_outbound),0) >= greatest(
  266. coalesce(SUM(s.air_outbound_perishable_shipments),0),
  267. coalesce(SUM(s.air_outbound_shipments),0) )
  268. THEN
  269. CASE
  270. WHEN ROUND(
  271. SUMIf(s.ocean_outbound , lower(s.incoterm) IN ('cif','cf','ci'))
  272. / NULLIF(SUM(s.overall),0) * 100 , 2 ) >= 15
  273. THEN 'OO_CIF' ELSE 'OO_FOB' END
  274.  
  275. WHEN coalesce(SUM(s.air_outbound_perishable_shipments),0)
  276. >= coalesce(SUM(s.air_outbound_shipments),0)
  277. THEN
  278. CASE
  279. WHEN ROUND(
  280. SUMIf(s.air_outbound_perishable_shipments ,
  281. lower(s.incoterm) IN ('cif','cf','ci'))
  282. / NULLIF(SUM(s.overall),0) * 100 , 2 ) >= 10
  283. THEN 'AO_P_CIF' ELSE 'AO_P_FOB' END
  284.  
  285. /* default: regular AO is biggest -------------------------------- */
  286. ELSE
  287. CASE
  288. WHEN ROUND(
  289. SUMIf(s.air_outbound_shipments ,
  290. lower(s.incoterm) IN ('cif','cf','ci'))
  291. / NULLIF(SUM(s.overall),0) * 100 , 2 ) >= 15
  292. THEN 'AO_CIF' ELSE 'AO_FOB' END
  293. END
  294. END AS trade_category,
  295. SUM(s.cn_in) AS cn_in_sum,
  296. SUM(s.sea_in) AS sea_in_sum,
  297. SUM(s.in_us) AS in_us_sum,
  298. SUM(s.in_gulf) AS in_gulf_sum,
  299. SUM(s.in_eafrica) AS in_eafrica_sum,
  300. SUM(s.in_wafrica) AS in_wafrica_sum,
  301. SUM(s.in_europe) AS in_europe_sum,
  302.  
  303. CASE
  304. WHEN SUM(s.cn_in) >= 480 THEN 'Super Large'
  305. WHEN SUM(s.cn_in) >= 120 THEN 'Large'
  306. WHEN SUM(s.cn_in) BETWEEN 48 AND 119 THEN 'Medium'
  307. WHEN SUM(s.cn_in) BETWEEN 9 AND 47 THEN 'Small'
  308. WHEN SUM(s.cn_in) BETWEEN 1 AND 8 THEN 'Micro'
  309. ELSE NULL
  310. END AS "China-India",
  311.  
  312. -- SEA-India
  313. CASE
  314. WHEN SUM(s.sea_in) >= 480 THEN 'Super Large'
  315. WHEN SUM(s.sea_in) >= 120 THEN 'Large'
  316. WHEN SUM(s.sea_in) BETWEEN 48 AND 119 THEN 'Medium'
  317. WHEN SUM(s.sea_in) BETWEEN 9 AND 47 THEN 'Small'
  318. WHEN SUM(s.sea_in) BETWEEN 1 AND 8 THEN 'Micro'
  319. ELSE NULL
  320. END AS "SEA-India",
  321.  
  322. -- India-US
  323. CASE
  324. WHEN SUM(s.in_us) >= 480 THEN 'Super Large'
  325. WHEN SUM(s.in_us) >= 120 THEN 'Large'
  326. WHEN SUM(s.in_us) BETWEEN 48 AND 119 THEN 'Medium'
  327. WHEN SUM(s.in_us) BETWEEN 9 AND 47 THEN 'Small'
  328. WHEN SUM(s.in_us) BETWEEN 1 AND 8 THEN 'Micro'
  329. ELSE NULL
  330. END AS "India-US",
  331.  
  332. -- India-Gulf
  333. CASE
  334. WHEN SUM(s.in_gulf) >= 480 THEN 'Super Large'
  335. WHEN SUM(s.in_gulf) >= 120 THEN 'Large'
  336. WHEN SUM(s.in_gulf) BETWEEN 48 AND 119 THEN 'Medium'
  337. WHEN SUM(s.in_gulf) BETWEEN 9 AND 47 THEN 'Small'
  338. WHEN SUM(s.in_gulf) BETWEEN 1 AND 8 THEN 'Micro'
  339. ELSE NULL
  340. END AS "India-Gulf",
  341.  
  342. -- India-EAfrica
  343. CASE
  344. WHEN SUM(s.in_eafrica) >= 480 THEN 'Super Large'
  345. WHEN SUM(s.in_eafrica) >= 120 THEN 'Large'
  346. WHEN SUM(s.in_eafrica) BETWEEN 48 AND 119 THEN 'Medium'
  347. WHEN SUM(s.in_eafrica) BETWEEN 9 AND 47 THEN 'Small'
  348. WHEN SUM(s.in_eafrica) BETWEEN 1 AND 8 THEN 'Micro'
  349. ELSE NULL
  350. END AS "India-EAfrica",
  351.  
  352. -- India-WAfrica
  353. CASE
  354. WHEN SUM(s.in_wafrica) >= 480 THEN 'Super Large'
  355. WHEN SUM(s.in_wafrica) >= 120 THEN 'Large'
  356. WHEN SUM(s.in_wafrica) BETWEEN 48 AND 119 THEN 'Medium'
  357. WHEN SUM(s.in_wafrica) BETWEEN 9 AND 47 THEN 'Small'
  358. WHEN SUM(s.in_wafrica) BETWEEN 1 AND 8 THEN 'Micro'
  359. ELSE NULL
  360. END AS "India-WAfrica",
  361.  
  362. -- India-Europe
  363. CASE
  364. WHEN SUM(s.in_europe) >= 480 THEN 'Super Large'
  365. WHEN SUM(s.in_europe) >= 120 THEN 'Large'
  366. WHEN SUM(s.in_europe) BETWEEN 48 AND 119 THEN 'Medium'
  367. WHEN SUM(s.in_europe) BETWEEN 9 AND 47 THEN 'Small'
  368. WHEN SUM(s.in_europe) BETWEEN 1 AND 8 THEN 'Micro'
  369. ELSE NULL
  370. END AS "India-Europe",
  371. arrayFilter(x -> x IS NOT NULL, [SUM(s.cn_in), SUM(s.sea_in), SUM(s.in_us), SUM(s.in_gulf),
  372. SUM(s.in_eafrica), SUM(s.in_wafrica), SUM(s.in_europe)]) AS lane_totals,
  373.  
  374. -- Determine the lane position based on the maximum value in lane_totals
  375. arrayMax(lane_totals) AS max_lane_value,
  376.  
  377. -- Assign sub_cohort based on the maximum lane value
  378. CASE
  379. WHEN arrayMax(lane_totals) = SUM(s.cn_in) THEN 'China-India'
  380. WHEN arrayMax(lane_totals) = SUM(s.sea_in) THEN 'SEA-India'
  381. WHEN arrayMax(lane_totals) = SUM(s.in_us) THEN 'India-US'
  382. WHEN arrayMax(lane_totals) = SUM(s.in_gulf) THEN 'India-Gulf'
  383. WHEN arrayMax(lane_totals) = SUM(s.in_eafrica) THEN 'India-EAfrica'
  384. WHEN arrayMax(lane_totals) = SUM(s.in_wafrica) THEN 'India-WAfrica'
  385. WHEN arrayMax(lane_totals) = SUM(s.in_europe) THEN 'India-Europe'
  386. END AS sub_cohort
  387. FROM orgs org
  388. LEFT JOIN shipments s ON s.lead_id = org.lead_organization_id OR s.registration_number = org.registration_number
  389. GROUP BY org.organization_id
  390. ),
  391. rfm_raw AS (
  392. SELECT
  393. lead_id,registration_number,
  394. groupArrayDistinct(shipment_year) AS years_present,
  395. CASE toUInt32(max(shipment_year))
  396. WHEN 2025 THEN 5
  397. WHEN 2024 THEN 4
  398. WHEN 2023 THEN 3
  399. WHEN 2022 THEN 2
  400. END AS recency_score
  401. FROM kavach.lead_shipment_commodity_stats_v2 final
  402. WHERE shipment_year IN (2022,2023,2024,2025)
  403. GROUP BY lead_id, registration_number
  404. ),
  405. rfm_cleaned AS
  406. (
  407. SELECT
  408. organization_id,
  409. MAX(recency) AS max_recency,
  410. MAX(frequency) AS max_frequency
  411. FROM (
  412. SELECT
  413. DISTINCT organization_id,
  414. MAX(recency_score) AS recency,
  415. multiIf(
  416. arraySort(r.years_present) = [2022, 2023, 2024, 2025], 5,
  417. arraySort(r.years_present) = [2023, 2024, 2025], 4,
  418. arraySort(r.years_present) = [2024, 2025], 3,
  419. arraySort(r.years_present) = [2022, 2023, 2024], 2,
  420. 1
  421. ) AS frequency
  422. FROM rfm_raw r
  423. LEFT JOIN orgs o
  424. ON r.lead_id = o.lead_organization_id
  425. OR r.registration_number = o.registration_number
  426. GROUP BY organization_id, r.years_present
  427. ) subquery
  428. GROUP BY organization_id
  429. )
  430. SELECT
  431. o.lead_organization_id,
  432. o.organization_id, business_name,
  433. registration_number,
  434. ind_contact,
  435. cse,
  436. shipment_status,
  437. enquiry_status,
  438. overall_strength,
  439. overall_strength_ea_oi AS "EA_OI",
  440. overall_strength_ea_ai AS "EA_AI",
  441. overall_strength_oo AS "OO",
  442. overall_strength_ao AS "AO",
  443. overall_strength_ao_p AS "AO_P",
  444. trade_category,
  445. sub_cohort AS trade_lane,
  446. "China-India",
  447. "SEA-India",
  448. "India-Gulf",
  449. "India-Europe",
  450. "India-EAfrica",
  451. "India-WAfrica",
  452. "India-US",
  453. CASE
  454. WHEN max_recency IS NULL THEN 1
  455. ELSE max_recency
  456. END AS recency,
  457. CASE WHEN
  458. max_frequency = 0 THEN 1
  459. ELSE max_frequency
  460. END AS frequency,
  461. CASE
  462. WHEN ihls_overall_strength = 'Super Large' THEN 5
  463. WHEN ihls_overall_strength = 'Large' THEN 4
  464. WHEN ihls_overall_strength = 'Medium' THEN 3
  465. WHEN ihls_overall_strength = 'Small' THEN 2
  466. WHEN ihls_overall_strength = 'Micro' THEN 1
  467. ELSE 1
  468. END AS monetary
  469. FROM orgs o
  470. LEFT JOIN stakeholders ss on o.organization_id = ss.organization_id
  471. LEFT JOIN aggregations s on o.organization_id = s.organization_id
  472. LEFT JOIN rfm_cleaned AS r ON r.organization_id = o.organization_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!