انبارگردانی


کوئری‌های شما باید روی آخرین نسخه‌ی MySQL قابل اجرا باشند.


محمد به‌تازگی به تیم مهندسی دیجی‌کال‍ا پیوسته. او در یکی از تسک‌هایش لازم دارد تا کوئری‌های مختلفی برای کار با داده‌هایی که با آن‌ها سروکار دارد بنویسد. داده‌هایی که محمد با آن‌ها سروکار دارد مربوط به محصولات و سفارش‌های مربوط به آن‌ها است. او یک denormalization روی داده‌ها اعمال کرده، به این‌صورت که علاوه بر اطلاعات سفارش‌های محصولات، مجموع فروش هر محصول نیز در جدول اطلاعات محصولات وجود دارد.

از آن‌جایی که محمد فرصت نوشتن کوئری‌های مدنظرش را ندارد، از شما می‌خواهیم تا این کوئری‌ها را برایش بنویسید.

جزئیات پروژه🔗

داده‌های اولیه برای تست نمونه را از این لینک دانلود کنید.

ساختار جداول به‌شرح زیر است:

جدول products: از این جدول برای نگه‌داری اطلاعات محصولات استفاده می‌شود. ساختار این جدول به‌صورت زیر است:

نام ستون نوع تعریف
id ‌BIGINT(20) شناسه‌ی محصول
name VARCHAR(255) نام محصول
description TEXT توضیحات محصول
price DECIMAL(15, 2) قیمت محصول
total_profit DECIMAL(15, 2) مجموع مبلغ فروش محصول
created_at TIMESTAMP زمان درج محصول

جدول orders: از این جدول برای نگه‌داری لیست سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:

نام ستون نوع تعریف
id ‌BIGINT(20) شناسه‌ی سفارش
user_id ‌BIGINT(20) شناسه‌ی کاربر سفارش‌دهنده
created_at TIMESTAMP زمان ایجاد سفارش

جدول order_details: از این جدول برای نگه‌داری اطلاعات اقلام موجود در سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:

نام ستون نوع تعریف
id ‌BIGINT(20) شناسه‌ی اطلاعات محصول در سفارش
order_id ‌BIGINT(20) شناسه‌ی سفارش
product_id ‌BIGINT(20) شناسه‌ی محصول
quantity ‌INT(11) تعداد سفارش‌داده‌شده‌ی محصول

جدول delivery_centers: از این جدول برای نگه‌داری اطلاعات مراکز ارسال استفاده می‌شود. ساختار این جدول به‌صورت زیر است:

نام ستون نوع تعریف
id ‌BIGINT(20) شناسه‌ی مرکز ارسال
name ‌VARCHAR(255) نام مرکز ارسال

جدول deliveries: از این جدول برای نگه‌داری اطلاعات تحویل سفارش‌ها استفاده می‌شود. ساختار این جدول به‌صورت زیر است:

نام ستون نوع تعریف
order_id ‌BIGINT(20) شناسه‌ی سفارش
delivery_center_id ‌BIGINT(20) شناسه‌ی مرکز ارسال
received_at ‌TIMESTAMP زمان دریافت محصولات سفارش از انبار
delivered_at ‌TIMESTAMP زمان تحویل سفارش به مشتری (در صورتی که سفارش هنوز تحویل داده نشده باشد، مقدار آن برابر با NULL خواهد بود.)

توجه داشته باشید که جدول کاربران صرفاً جهت سادگی در سؤال نیامده است. نیازی به اطلاعات چنین جدولی نیست.

مطلوبات🔗

کوئری‌های زیر را طوری بنویسید که خروجی خواسته‌شده به‌دست آید:

  1. محمد اکنون متوجه شده که یک باگ در بک‌اند برنامه‌اش وجود داشته و مقدار ستون total_profit در جدول products لزوماً مطابق با اطلاعات موجود در جداول orders و order_details نیست (در داده‌ها inconsistency وجود دارد). کوئری‌ای بنویسید که مقدار ستون total_profit جدول products را طبق مقادیر موجود در جداول orders و order_details به‌روزرسانی کند تا inconsistency در صورت وجود رفع شود.
  2. کوئری‌ای بنویسید که شناسه‌ی ۵ مرکز ارسال برتر از نظر چابک بودن را برگرداند. یک مرکز ارسال در صورتی چابک است که میانگین اختلاف زمانی بین دریافت محصول از انبار تا تحویل آن به مشتری در سفارش‌های تحویل‌داده‌شده‌اش کمتر از سایر مراکز ارسال باشد. نتایج را به‌ترتیب نزولی میزان چابک بودن مرتب کنید. تضمین می‌شود که میزان چابک بودن مراکز ارسال با یکدیگر متفاوت است. نام ستون خروجی اهمیتی ندارد.
  3. کوئری‌ای بنویسید که شناسه‌ی محصولاتی که تا ۷ روز پس از انتشارشان کمتر از ۱۰ فروش داشتند و حدقل ۱ فروش داشتند را به‌ترتیب صعودی شناسه‌ی محصول دریافت کند. نام ستون خروجی اهمیتی ندارد.

نکات🔗

  • کوئری‌های شما باید روی آخرین نسخه‌ی MySQL قابل اجرا باشند.
  • کوئری هر بخش باید تنها شامل یک statement باشد.
  • هر کوئری امتیاز جداگانه دارد و اگر کوئری یک قسمت را نتوانستید بنویسید، کوئری‌هایی که حل کردید را بفرستید و قسمت آن کوئری را خالی بگذارید.
  • در تست نمونه‌ی این سؤال، فقط کوئری بخش اول تست می‌شود.

آن‌چه باید آپلود کنید🔗

پس از طراحی کوئری‌ها، آن‌ها را در قالب زیر در یک فایل با پسوند .sql آپلود کنید.

-- Section1
    Your first query here
-- Section2
    Your second query here
-- Section3
    Your third query here
SQL